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.
A courtesy of https://blog.jooq.org/10-sql-tricks-that-you-didnt-think-were-possible/ :
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