Adjusting a table column value by subtracting previous running total from current row value

I have worked on this issue and looked for similar threads but could not find any solution yet. I have tried couple ways which i will explain and give an example of the data and what needs to be achieved:

I have a table that looks like this :

drop table if exists #temp  create table #temp (col1 varchar(10), col2 varchar(10),col3 varchar(10), [date]  date,dateindex int, totals money, adjustement  money )    insert into #temp values  ('customer1' ,'customer1','customer1', '01-01-2020',1,0,100)                          , ('customer1' ,'customer1','customer1', '01-02-2020',1,0,200)                          , ('customer1' ,'customer1','customer1', '01-03-2020',1,50,0)                          , ('customer1' ,'customer1','customer1', '01-03-2020',2,100,0)                          , ('customer2' ,'customer2','customer2', '01-04-2020',1,0,150)                          , ('customer2' ,'customer2','customer2', '01-05-2020',2,0,300)                          , ('customer2' ,'customer2','customer2', '01-06-2020',1,50,0)                          , ('customer2' ,'customer2','customer2', '01-06-2020',2,100,0)  

we have two numeric columns : totals and adjustments , the rest is unique attributes for a customer and a date. when adjustments are different than 0 , that is fine and we don't need to change those numbers. when totals are different than 0, that is when I want to do a calculation and hopefully update the result to Adjustments field on the same row ( when total is <> 0 ,adjustments always = 0 ).

the calculation to replace those 0 adjustments should be : the current total value - all previous adjustments (running total) this is what i have tried :

select *           ,case when totals <> 0                 then totals - sum(adjustement) over (partition by col1,col2,col3                                                           order by [date] asc,dateindex asc rows between unbounded preceding and 1 preceding)                 else adjustement                 end as 'Calculation'      from #temp  

row 3 returns correct result : 50 - 300 = -250

now, when you have totals on the same day (row 3 and 4 ), same date but different index (index used to know which transaction came first), the window function is doing row4 = 100 -(100+200+0) =-200 when it needs to be 100 - (100+200-250+0) = 50 .

the previous -250 is not included because its not the actual data .I need to find way to update the table while doing the calculation so that -250 can be updated on the table before moving the next row.

this calculation is done for each customer and that is why I am partitioning by customer columns.

I really appreciate any hints or advice on this and what could be the best approach

thanks !

https://stackoverflow.com/questions/65929618/adjusting-a-table-column-value-by-subtracting-previous-running-total-from-curren January 28, 2021 at 09:03AM