May 28, 2013 at 12:06 am
Create Table #Temp
(
Number Int,
Totalcoll Numeric(12,2),
Maintainance_Due Numeric(12,2),
Maintainance_Coll Numeric(12,2),
Expense_Due Numeric(12,2),
Expense_Coll Numeric(12,2)
)
Insert Into #Temp
Values (1,0,500,0,400,0),
(1,900,0,0,0,0),
(1,0,200,0,800,0),
(1,1000,0,0,0,0)
Select * From #Temp
/*
With reference to above data,
my Requirement is,
For 1st row, there is Maintainance_Due = 500 and Expense_Due = 400
then In Second row,I want Under Maintainance_Coll should be 500 and Expense_Coll = 400
(This is TotalColl of 900 is allcated to Maintainance_Coll since Maintainance_Due = 500 and Expense_Due = 400)
Same as this in 3rd row there is Maintainance_Due = 200 and Expense_Due = 800
So,In fourth row,I want Under Maintainance_Coll should be 200 and Expense_Coll = 800
(This is TotalColl of 100 is allcated to Maintainance_Coll since Maintainance_Due = 200 and Expense_Due = 800)
*/
Please Help in this rewuirement as I required this in urgent and I am stucked in this.
Please.........
Thanks in Advance!!!!!!!!!!!!!!
May 28, 2013 at 12:31 am
You don't mention how the rows are related to the next row. I have numbered all rows, but this is maybe not as you need. The numbering is needed to relate a specific row to the next row.
Create Table #Temp
(
Number Int,
Totalcoll Numeric(12,2),
Maintainance_Due Numeric(12,2),
Maintainance_Coll Numeric(12,2),
Expense_Due Numeric(12,2),
Expense_Coll Numeric(12,2)
)
Insert Into #Temp
Values (1,0,500,0,400,0),
(1,900,0,0,0,0),
(1,0,200,0,800,0),
(1,1000,0,0,0,0)
;with cte_rownumber as
(select
ROW_NUMBER() over (order by Number) as rownr
, *
from #Temp
)
select
current_row.Totalcoll
, current_row.Maintainance_Due
, case when current_row.Maintainance_Due = 0
then next_row.Maintainance_Due
else current_row.Maintainance_Coll
end as Maintainance_Coll
, current_row.Expense_Due
, case when current_row.Expense_Due = 0
then next_row.Expense_Due
else current_row.Expense_Coll
end as Expense_Coll
from cte_rownumber as current_row
left outer join cte_rownumber as next_row
on current_row.rownr = next_row.rownr +1
drop table #Temp
Depending on the value in column [Maintenance_Due] the value in column [Maintenance_Coll] is filled with the value of the current or the previous row.
May 28, 2013 at 1:01 am
Create Table #Temp
(
Number Int,
Transaction_Date DateTime,
Totalcoll Numeric(12,2),
Maintainance_Due Numeric(12,2),
Maintainance_Coll Numeric(12,2),
Expense_Due Numeric(12,2),
Expense_Coll Numeric(12,2)
)
Insert Into #Temp
Values (1,GetDate(),0,500,0,400,0),
(1,DateAdd(dd,1,GetDate()),900,0,0,0,0),
(1,DateAdd(dd,2,GetDate()),0,200,0,800,0),
(1,DateAdd(dd,3,GetDate()),1000,0,0,0,0),
(2,DateAdd(dd,4,GetDate()),0,100,0,400,0),
(2,DateAdd(dd,5,GetDate()),450,0,0,0,0),
(2,DateAdd(dd,6,GetDate()),0,400,0,500,0),
(2,DateAdd(dd,7,GetDate()),800,0,0,0,0)
Select * From #Temp
/*
Thanks the code given by you is correct,but I want few more thing to add
Now Consider the revised data it is same with extra details,the transaction are occuring datewise,
1)Now for Number 2, on 2013-06-01,Maintainance_Due = 100 and Expense_Due = 400
But on 2013-06-02 TotalColl = 450,so it should be allocated as Expense_coll = 400 (Since Expense_Due = 400)
and Maintainance_Coll = 50 (Since remaining coll is 50) so the Maintainance_Due of 50 should be carry forward to
next Maintainance_Due (that is on 2013-06-03 Maintainance_Due should be 450 (Originally 400 and carryforwarded 50))
*/
Actually this is the account concept of Allocation,
for this I want to write the SQL Programme
Please help me out.
Thanks in Advance !!!!!!!!!!!
May 28, 2013 at 1:55 am
I don't have a complete solution for you (not enough time on my hands), but I think the code below could give you a headstart.
Create Table #Temp
(
Number Int,
Transaction_Date DateTime,
Totalcoll Numeric(12,2),
Maintainance_Due Numeric(12,2),
Maintainance_Coll Numeric(12,2),
Expense_Due Numeric(12,2),
Expense_Coll Numeric(12,2)
)
Insert Into #Temp
Values (1,GetDate(),0,500,0,400,0),
(1,DateAdd(dd,1,GetDate()),900,0,0,0,0),
(1,DateAdd(dd,2,GetDate()),0,200,0,800,0),
(1,DateAdd(dd,3,GetDate()),1000,0,0,0,0),
(2,DateAdd(dd,4,GetDate()),0,100,0,400,0),
(2,DateAdd(dd,5,GetDate()),450,0,0,0,0),
(2,DateAdd(dd,6,GetDate()),0,400,0,500,0),
(2,DateAdd(dd,7,GetDate()),800,0,0,0,0)
;with cte_rownumber as
(select
ROW_NUMBER() over (partition by Number order by Transaction_Date) as rownr
, *
from #Temp
)
select
current_row.Number
, current_row.Transaction_Date
, current_row.Totalcoll
, current_row.Maintainance_Due
, case when current_row.Maintainance_Due = 0
then current_row.Totalcoll - next_row.Expense_Due
else current_row.Maintainance_Coll
end as Maintainance_Coll
, current_row.Expense_Due
, case when current_row.Expense_Due = 0
then next_row.Expense_Due
else current_row.Expense_Coll
end as Expense_Coll
, case when current_row.Totalcoll > 0
then (next_row.Maintainance_Due + next_row.Expense_Due) - current_row.Totalcoll
else current_row.Totalcoll
end as remaining
from cte_rownumber as current_row
left outer join cte_rownumber as next_row
on current_row.rownr = next_row.rownr +1
and current_row.number = next_row.Number
drop table #Temp
Try to figure out something yourself with above code sample. Hope this will help.
May 31, 2013 at 8:18 am
Hi,
with reference to previous communication,I have stucked once again,
Attached Herewith is the excel sheet with reference data.
Please help me!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply