CODEWITHSUNDEEP
sql-serverexcellaglead
Jans
Jans

Reputation: 17

SQL Server query problem. example is in excel sheet picture

Please see the following pic and i want to convert this formula in SQL Server. in excel sheet

        M   N
    15  1   0
    16  3   1
    17  5   2
    18  8   4
    19  9   4


    N= IF(M16-M15<=1,N15,M16-M15-1+N15

Please see the screenshot for reference:

Excel formula for calculation required in SQL Server

Upvotes: 0

Views: 59

Answers (1)

seanb
seanb

Reputation: 6685

As per your tags, this can be done with LAG and then doing a running total.

  • For each row, first calculate the difference in M from the previous row (using LAG) - I call this Dif_Last_M. This mirrors the 'M24-M23' part of your formula.
  • If Dif_Last_M is <= 1, add 0 to the running total (effectively making the running total the same as for the previous row)
  • Else if Dif_Last_M is > 1, add (Dif_Last_M minus 1) to the running total

Here is the code assuming your source table is called #Temp and has an ID (sorting value)

WITH M_info AS
    (SELECT  ID, M, (M - LAG(M, 1) OVER (ORDER BY ID)) AS Dif_Last_M
        FROM #Temp
    )
SELECT  ID, 
        M, 
        SUM(CASE WHEN Dif_Last_M > 1 THEN Dif_Last_M - 1 ELSE 0 END) OVER (ORDER BY ID) AS N
FROM    M_info;

And here are the results

ID  M   N
1   1   0
2   3   1
3   5   2
4   8   4
5   9   4
6   12  6
7   13  6

Here is a db<>fiddle with the above. It also includes additional queries showing

  • The result from the CTE
  • The values used in the running total

Note that while it possible to do this with recursive CTEs, they tend to have performance problems (they are loops, fundamentally). Soit is better (performance-wise) to avoid recursive CTEs if possible.

Upvotes: 1

Related Questions