t-sqlssmscumulative-sumpartition-by

Calculate the cumulative total between 2 columns until a non-zero value is reached in 1st column. Once non-zero value is reached, the sum restarts


I am querying a database using TSQL in SSMS.I have a dataset that contains two unique ID's, A and B. For each of these ID's I want to sum columns OAC and Adj cumulatively until the next non-zero value is reached in column OAC. In other words, the non-zero values on column OAC will remain the same while the values between them will be adding consequent column Adj values using the first non-zero OAC value as a stop and restart point.

The data table can be created using the below

drop table #T

CREATE TABLE #T(

    Id varchar(10),
    PeriodNum int,
    row_num int,
    OAC  money  ,
    Adj  money




) ON [PRIMARY]
GO


insert into #T
values

('A','201606','1','5','0'),
('A','201905','2','0','-2'),
('A','201906','3','100','0'),
('A','202008','4','0','-6'),
('A','202009','5','0','-8'),
('A','202106','6','0','-11'),
('A','202109','7','23','0'),
('B','201606','1','3','0'),
('B','201905','2','0','25'),
('B','201906','3','60','0'),
('B','202008','4','0','12'),
('B','202009','5','0','-5'),
('B','202106','6','0','6'),
('B','202109','7','6','0')

I tried the following code to calculate the desired result

select * , sum( iif(t.OAC<> 0,(t.OAC + t.Adj),0)) over (partition by t.ID      order by t.row_num asc) as Calc
from #T t

This did not work as the needed result looks as per below in the calc column.

ResultSet


Solution

  • A courtesy of https://blog.jooq.org/10-sql-tricks-that-you-didnt-think-were-possible/ :

    enter image description here

    Of course, that vendor is not Microsoft, so we're stuck to less finessed options.

    One way to go about this is to first use a conditional aggregate on a flag to create "groupings", let's call that [sumstep], and then partition on that to have separate running totals:

    ;with cte as
    (
    select *,sum(case when OAC=0 then 0 else 1 end) over (partition by id order by row_num) as sumstep
    from #t
    )
    select *,sum(OAC+Adj) over (partition by id,sumstep order by row_num) as Calc
    from cte