SQL Server Subtract a value from records in the order of first to last

user_11987519 0
Viewed 55

I have a table temp with 2 columns

create table temp
(
    id int identity(1,1),
    amount decimal(18,2)
)

Sample data insert as follows

insert into temp(amount) 
values (100), (200), (500)

Table will look like

id amount
-----------
1  100
2  200
3  500

What I am trying to achieve is if suppose we deduct an amount of 150 from the table then deduction should happen in the order of Id. Which means the amount of id 1 will be 0 (100-150 =0 and remaining is 50) and then amount of id 2 will be 150 (balance 50 from previous deduction must be reduced from 200)

So the result data set should be

id amount
---------
1  0
2  150
3  500
tsql sql-server sql
1 Answers

Window cumulative summation for all previous records, save to cte for aliasing:

create table #a(id int,amount int)

insert #a values(1,100),(2,200),(3,500)

select * from #a

declare @sub int=150
;with cte as
(
    select id,amount,isnull(sum(amount) over (order by id rows between unbounded preceding and 1 preceding),0) as prev_sum
    from #a
)
select *,case
            when @sub-prev_sum>amount then 0
            when @sub-prev_sum>0 then amount-(@sub-prev_sum)
            else amount
         end
from cte
user_58259630
Related