SQL Puzzle | The Row Number Puzzle

In this puzzle you need to generate range based on the end number of the previous row plus a value. 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 – SQL 2012 +

 ``` -- -- /*------------------------ ;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 ------------------------*/ BudgetId AdminFeeId SupplementNbr MonthsToFundNbr FundAmt ----------- ----------- ------------- --------------- ----------- 614 70 1 2 8500 614 71 3 6 8000 721 145 7 12 7500 856 156 13 16 6500 -- ```

Solution 2 – Below SQL 2012

 ``` -- ;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