SQL Server : conditional aggregate ;

Question

I have a table that looks like this:

  Year       Value
  -----------------
  2013      -0.0016
  2014      -0.0001
  2015       0.0025
  2016      -0.0003
  2017       0.0023
  2018       0.0002

And I need to perform a conditional aggregate that results in a new column. The conditions are as follows:

If the value is negative then the aggregation starts and doesn't stop until the value is positive. Then nothing until the value is negative again... The result will look like this:

  Year       Value        AggCol
  2013      -0.0016      -0.0016
  2014      -0.0001      -0.0017
  2015       0.0025       0.0008
  2016      -0.0003      -0.0003
  2017       0.0023       0.002
  2018       0.0002       0.0002

This udf is as close as I have gotten:

create function dbo.fn(@cYear numeric, @rate float)
returns float
as 
begin
    declare @pYear numeric
    declare @return float

    set @pYear = @cYear - 1

    set @return = (select 
                        case 
                            when Value < 0 and @rate > 0  then null 
                            when Value < 0 then Value + @rate
                            else @rate 
                        end 
                   from Table1 
                   where [year] = @pYear)

    return @return
end

I am ok with an answer in c# if that would be easier but prefer SQL. The issue with the function I made is I need to be able to grab the results from the previous row to add to value when the value is positive.

I was up all night on here searching for clues and no joy...

EDIT: So think of these as CPI values for the year to be applied to your cellphone bill by your carrier... They are only going to increase your bill by the CPI, and never decrease it (if CPI is negative)... but they will offset the previous years negative CPI by the current years CPI if the Current year CPI is positive (or the sum results in a positive)...

That may or may not help but that is the situation lol.

Answer 1

DECLARE @t TABLE ( [Year] INT, Value MONEY )

INSERT  INTO @t
VALUES  ( 2013, -0.0016 ),
        ( 2014, -0.0001 ),
        ( 2015, 0.0025 ),
        ( 2016, -0.0003 ),
        ( 2017, 0.0023 ),
        ( 2018, 0.0002 )

SELECT  t1.Year ,
        t1.Value ,
        oa.AggCol
FROM    @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    AggCol
2013    -0.0016  -0.0016
2014    -0.0001  -0.0017
2015    0.0025   0.0008
2016    -0.0003  -0.0003
2017    0.0023   0.002
2018    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.