SQL Server : conditional aggregate ;
DECLARE @t TABLE ( [Year] INT, Value MONEY )INSERT INTO @tVALUES ( 2013, -0.0016 ), ( 2014, -0.0001 ), ( 2015, 0.0025 ), ( 2016, -0.0003 ), ( 2017, 0.0023 ), ( 2018, 0.0002 )SELECT t1.Year , t1.Value , oa.AggColFROM @t t1 OUTER APPLY ( SELECT SUM(Value) AS AggCol FROM @t t2 WHERE Year <= t1.Year AND Year > ( SELECT ISNULL(MAX(Year), 0) FROM @t WHERE Year < t1.Year AND Value > 0) ) oa
Year Value AggCol2013 -0.0016 -0.00162014 -0.0001 -0.00172015 0.0025 0.00082016 -0.0003 -0.00032017 0.0023 0.0022018 0.0002 0.0002
That means: for each row give me a sum of values less or equal than current row and greater than maximal row with positive value that appears before current row, or starting from 0 if no such found.
You can also do it using window functions:
;WITH PrevValues AS ( SELECT Year, Value, LAG(Value) OVER (ORDER BY Year) AS prevValue FROM Table1), Flags AS ( SELECT Year, Value, CASE WHEN Value < 0 AND prevValue > 0 THEN 2 -- next slice WHEN Value < 0 OR prevValue < 0 THEN 1 -- same slice WHEN Value > 0 AND prevValue > 0 THEN -1 -- not in a slice END AS flag FROM PrevValues), Islands AS ( SELECT Year, Value, CASE WHEN flag = -1 THEN -1 ELSE SUM(flag) OVER (ORDER BY Year) - ROW_NUMBER() OVER (ORDER BY Year) END AS grp FROM Flags)SELECT Year, Value, CASE WHEN grp = -1 THEN Value ELSE SUM(Value) OVER (PARTITION BY grp ORDER BY Year) END AS AggColFROM IslandsORDER BY Year
The idea is to identify islands of rows over which the running sum is applied.
DECLARE @t TABLE ( [Year] INT, Value MONEY )INSERT INTO @tVALUES (2013,-0.0016),(2014,0.0001),(2015,0.0025),(2016,-0.0003),(2017,0.0023),(2018,0.0002);WITH cteRowNum AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY Year) as RowNum FROM @t), cteRecursive AS ( SELECT Year ,Value ,Value as AggCol ,RowNum FROM cteRowNum WHERe RowNum = 1 UNION ALL SELECT c.Year ,c.Value ,CASE WHEN AggCol >= 0 THEN c.Value ELSE AggCol + c.Value END ,c.RowNum FROM cteRecursive r INNER JOIN cteRowNum c ON r.RowNum + 1 = c.RowNum)SELECT Year, Value, AggColFROM cteRecursive
NOTE THIS IS A DIFFERENT DATA SET THAN WHAT YOU PROVIDED! here are the results
Year Value AggCol2013 -0.0016 -0.00162014 0.0001 -0.00152015 0.0025 0.0012016 -0.0003 -0.00032017 0.0023 0.0022018 0.0002 0.0002
The problem with your original test data is that it did not account for the situation where it would take multiple sequential positive records to make the running sum positive. Subsequently BOTH other answers at the time I am posting my answer are wrong. So I changed only the 2014 record to positive .0001 and you can see how this solution works and the others do not.
There are probably ways of doing this with window functions but the recursive cte is pretty straight forward so I went that route:
- First build a row_number on the dataset to use in the joins to account for situation if a YEAR is missing in your dataset or something.
- Next build the recursive cte and step 1 row at a time using the row number and determine if the aggregate value needs to be reset or added to based on whether the previous rows value is positive or negative.
Here are the results of Giorgos & Giorgi's answers if you make the change to the test data:
Year Value AggCol2013 -0.0016 -0.00162014 0.0001 -0.00152015 0.0025 0.00252016 -0.0003 -0.00032017 0.0023 0.0022018 0.0002 0.0002
You can see the problem with the AggCol for Year 2015 is wrong
Please note I think there answers are great attempts and show some real skill/code when it comes to gaps/islands. I am not trying to attack simply enhance the quality of the post.