Click here to Skip to main content
15,890,282 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi Friends,

I want to subtract the value of the column in each row if the row is not enough then continue to next row.

For example

SQL
ID             Amt      Date               Total

A               20      March 5 2015             20

B               40      Feb 21 2015              40

C               60      Feb 2  2015              60  


I want to update this table by subtract the value of the column (Amt) out 70 based on date for example from march 10 - feb 10 and update the remaing to next row so the result i want will be
SQL
ID              Amt                Total

A                20 - 70 = -50 -->  0 this row will be updated to 0 and 50 will continue to next row   

B                40 - 50  = -10 -->  0   this row will be updated to 0 and 10 will continue to next row

C                60 - 10  = 50  -->  Stop loop


How can i write the sql query for this operation ,

I tried using Rows UNBOUNDED PRECEDING its not supporting in SQL 2008 R2

Thanks
Posted
Updated 23-Mar-15 21:11pm
v4
Comments
RossMW 24-Mar-15 20:27pm    
Have you considered a stored procedure cursor with a variable for remainder amount to be carry over from row to row?

use the following Query

SQL
declare @tb table(id char,amount int,[date] date, total int)
insert into @tb(ID,amount,[Date],Total) values
('A',20,'March 5 2015',20),
('B',40,'Feb 21 2015',40),
('C',60,'Feb 2  2015',60)

declare @amt int=70

;with cte as (select *,ROW_NUMBER() over(order by [date] desc ) as rno from @tb t),
cte1(rno,tot) as (select top 1 rno, @amt-amount as tot from cte t
union all select t.rno, tot-amount as tot from cte t,cte1  where (t.rno-1)=cte1.rno
)
update t set total=(case when tot>0 then 0 else -tot end) from cte t inner join cte1 on t.rno=cte1.rno

select * from @tb
 
Share this answer
 
see this Example[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900