Tags

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


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

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 – 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