Suppose I had this following table in SQL Server:

I want to calculate the cumulative sum for col C only in the case of positive result else 0

select *
    , sum(colC) over (partition by colA order by colB asc rows between unbounded preceding and current row) as colD
from tableA

If I am using above query it is resulted as:

but what I want is:

How can I achieve this in SQL query?

CodePudding user response：

You can go for a derived table and apply CASE expression as given below:

declare @t table( colA  char(2), colB   int, colC int)

insert into @t 
values
('a1',1     ,-1 )  
,('a1',2    ,-1 )
,('a1',3    ,12 )
,('a1',4    ,2)
,('a1',5    ,-45);


;with cte_colD as
(
SELECT colA,colB,ColC, CASE WHEN colc < 0 then 0 else colC end as ColD
from @t
where colB =1 
union all
SELECT t.colA,t.colB,t.ColC, CASE WHEN (c.colD   t.colC) <0 then 0 else (c.colD   t.colC) end as ColD
from @t as t
INNER JOIN cte_colD as c
on t.colA = c.colA
and t.colB = c.colB   1
)
select * from cte_colD

CodePudding user response：

declare @t table( colA  varchar(50), colB   int, colC int)

insert into @t 
values
('a1',1     ,-1 )  
,('a1',2    ,-1 )
,('a1',3    ,12 )
,('a1',4    ,2)
,('a1',5    ,-45);

with t as (
select colA,colB,ColC
,sum(IIF(ColC < 0,0,ColC)) over (partition by colA order by colB asc rows between unbounded preceding and current row) as colD
FROM @t)

select colA,colB,ColC,
IIF(ColC<0,0,ColD) ColD
from t
order by colB