Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

BudgetId AdminFeeId SupplementNbr MonthsToFundNbr FundAmt
614 70 0 2 8500
614 71 0 4 8000
721 145 1 6 7500
856 156 2 4 6500

Expected Output

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

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”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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