4

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

enter image description here

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

The formula for change is:

enter image description here

While the formula for new_threshold is:

enter image description here

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:

enter image description here

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.

  • And your version of sql server is ... sql server 2012 ? (and it is quicker if you are adding the insert statement for this table) – Sabin Bio Aug 30 '17 at 11:10
  • @sabinbio Yes. MSSQL 2012. I can also add the insert statement – Nilzone- Aug 30 '17 at 11:29
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
  • Interesting idea! :) Only thing though. Your're selecting [new_threshold] in both of your CTE's. This is supposed to be calculated. I see my insert statement was a little bit confusing. I'll update it. Sorry about that. – Nilzone- Aug 30 '17 at 12:05
  • Updated now. Again, sorry about that. – Nilzone- Aug 30 '17 at 12:08
  • You're welcome! is my pleasure – Sabin Bio Aug 30 '17 at 13:50
  • Just curious: would it be possible to achieve this using aggregation with window-functions (over-clause), or it recursion the best (or only) option here? – Nilzone- Aug 30 '17 at 17:20
  • We can try... it was my first try, but I stumble on something and I change "tactic" to use recursive. Because you are calculating the field change base on the previous threshold, which is also calculated... – Sabin Bio Aug 30 '17 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.