SQL Puzzle | The Nbr(Range) Puzzle

In this puzzle we have to generate the columns (SupplementNbr and MonthsToFundNbr) as per below logic.

SupplementNbr = 1 + last row of MonthsToFundNbr.
MonthsToFundNbr = SupplementNbr + MonthsToFundNbr

Please check out the sample input values and sample expected output below.

Sample Input

614 70 0 2 8500
614 71 0 4 8000
721 145 1 6 7500
856 156 2 4 6500

Expected Output

614 70 1 2 8500
614 71 3 6 8000
721 145 7 12 7500
856 156 13 16 6500

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE Budget ( BudgetId INT ,AdminFeeId INT ,SupplementNbr INT ,MonthsToFundNbr INT ,FundAmt INT ) GO INSERT INTO Budget VALUES (614, 70 , 0 , 2 , 8500 ), (614, 71 , 0 , 4 , 8000 ), (721, 145 , 1 , 6 , 7500 ), (856, 156 , 2 , 4 , 6500 ) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution 1 – With LAG

 ``` -- ;WITH CTE AS ( SELECT * ,ROW_NUMBER() OVER (ORDER BY AdminFeeId) rnk FROM Budget ) ,CTE1 AS ( SELECT BudgetId,AdminFeeId , SUM(MonthsToFundNbr) OVER (ORDER BY rnk) MonthsToFundNbr ,FundAmt ,rnk FROM CTE c ) SELECT BudgetId,AdminFeeId , ISNULL(LAG(MonthsToFundNbr) OVER (ORDER BY rnk),0)+1 SupplementNbr ,MonthsToFundNbr,FundAmt FROM CTE1 -- ```

Solution 2 – Without LAG

 ``` -- ;WITH CTE AS ( SELECT * ,ROW_NUMBER() OVER (ORDER BY AdminFeeId) rnk FROM Budget ) ,CTE1 AS ( SELECT BudgetId,AdminFeeId , SUM(MonthsToFundNbr) OVER (ORDER BY rnk) MonthsToFundNbr ,FundAmt ,rnk FROM CTE c ) SELECT c.BudgetId,c.AdminFeeId , ISNULL(( SELECT TOP 1 MonthsToFundNbr FROM CTE1 c1 WHERE c1.rnk < c.rnk ORDER BY c1.rnk DESC ),0)+1 SupplementNbr ,c.MonthsToFundNbr,c.FundAmt FROM CTE1 c -- ```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com