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

Output:

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.

Demo here

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.