Adjusting a column value by subtracting previous running total from current row value in SQL Server

Question

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 :

Code:

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 is different from 0, that is fine and we don't need to change those numbers.

When totals is different from 0, that is when I want to do a calculation and hopefully update the result of the Adjustments column in the same row (when total is <> 0, adjustments is 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 the 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 !

question from:https://stackoverflow.com/questions/65929618/adjusting-a-column-value-by-subtracting-previous-running-total-from-current-row

Answer 1

I am not quite sure how the third index would work on a date. But for the second index (as in your example), you can do the calculation twice:

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

I suspect the third calculation just requires another level of subquery.

Here is a db<>fiddle.