SQL Server : keep track of a running calculation monthly

Question

I have the following in Excel which I need to calculate in SQL Server:

The columns that needs to be calculated is change and new_threshold:

The formula for change is:

While the formula for new_threshold is:

I have tried to use the lead and lag methods SQL Server offers, but I can't seem to keep track of the calculation.

Here is one thing I've tried:

SELECT
    year_month,
    round(original_threshold, 0) original_threshold,
    round(case when original_threshold > isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)
        then isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)  - original_threshold
        else 0 end, 0) change,
    manualChange,
    round(original_threshold + case when original_threshold > isnull(lead(original_threshold) over (order by [program], [year_month] desc), original_threshold)
        then isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)  - original_threshold
        else 0 end + manualChange, 0) new_threshold
FROM 
    [Test].[dbo].[test]

which yields:

Edit:

Here is the insert statement if you want to test it

insert into [Test].[dbo].[test]
(
     [year_month]
    ,[original_threshold]
    ,[manualChange]
)

values
('2017_09', 401,    0),
('2017_08', 396,    0),
('2017_07', 397,   -5),
('2017_06', 389,   -7),
('2017_05', 393,    0),
('2017_04', 390,    0),
('2017_03', 412,    0),
('2017_02', 439,    0),
('2017_01', 458,    0)

Based on the values from this insert, I want to be able to calculate the change and new_threshold like in the Excel above.

Answer 1

Here is my idea: -I used recursive approach, - for this , added a Row_number, used this to join previous records

;WITH cte_Source AS
(SELECT 
    year_month
    ,original_threshold  
    ,manualChange
    ,CONVERT(int,LEFT(year_month,4)) As mYear
    ,CONVERT(int,RIGHT(year_month,2)) As mMonth
    ,ROW_NUMBER()OVER(ORDER BY CONVERT(int,LEFT(year_month,4)),CONVERT(int,RIGHT(year_month,2))) AS RN
FROM
    #Test AS T
)
,cteR AS
(
    SELECT TOP(1)
        T.year_month
        ,T.original_threshold  
        ,T.manualChange
        ,0 AS change
        ,T.original_threshold  +  0 + T.manualChange AS  new_threshold
        ,T.RN
    FROM cte_Source AS T
    --WHERE T.year_month ='2017_01'
    ORDER BY T.mYear * 100 + T.mMonth ASC


    UNION ALL

    SELECT
        S.year_month
        ,S.original_threshold  
        ,S.manualChange
        ,CASE WHEN  S.original_threshold > R.new_threshold THEN R.new_threshold - S.original_threshold ELSE 0 END AS  change
        ,S.original_threshold  
            + CASE WHEN  S.original_threshold > R.new_threshold THEN R.new_threshold - S.original_threshold ELSE 0 END 
            + S.manualChange AS  new_threshold
        ,S.rn AS rn
        FROM cte_Source AS S
            INNER JOIN cteR AS R
            ON r.RN + 1 = S.RN 

)

select  
    [year_month]
    ,[original_threshold]
    ,[change]
    ,[manualChange]
    ,[new_threshold]    
from cteR

output for it:

year_month original_threshold change      manualChange new_threshold
2017_01    458                0           0            458
2017_02    439                0           0            439
2017_03    412                0           0            412
2017_04    390                0           0            390
2017_05    393                -3          0            390
2017_06    389                0           -7           382
2017_07    397                -15         -5           377
2017_08    396                -19         0            377
2017_09    401                -24         0            377