asked on

Need to generate range based on the end number of the previous row plus a value

SQL 2010:

With the following Is there a way within a view to create the results below?

BudgetId      AdminFeeId      SupplementNbr      MonthsToFundNbr      FundAmt
614        70        0        2        8500            The first budget (614) has two entries for admin fee
614       71        0        4        8000
721 145        1        6        7500            The second budget has one entry for Admin
856        156        2        4        6500            The third budget has one entry for Admin

I need the results to look like the following:

BudgetId      AdminFeeId      SupplementNbr      StartRangeNbr      EndRangeNbr      FundAmt
614        70        0        1        2        8500
614        71        0        3        6        8000        Start range is 1 plus last row;
End Range =
Start Range plus MonthsToFundNbr
721        145        1        7        12        7500
856        156        2        13        16        6500

I have tried cross join, correlated sub query, OVER Partition.
I may just being getting the syntax wrong.

Pawan Kumar

Please find the solution below-

Create table and data insertion

--

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

--

Open in new window

Solution

--

;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


--

Open in new window

Output

--

/*------------------------
;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

(4 row(s) affected)


--

Open in new window

Cheers !

HainKurt

we need to see queries for those results...

and

post your queries + result of those and the result you are looking for in code block please...

this way it looks very messy...

Sue Tippett

ASKER

I knew the answer had to do with Row_Number. Since the values a never the same from one invoice to another. Thank you for taking the time to provide an excellent solution.

Sue Tippett

ASKER

This was the best solution since the working with dynamic data. Thank you for your response

Sue Tippett

ASKER

Getting a error within CTE1 by the ORDER and LAG is not available even through the we are using SQL 2012. IT will not change the COMPATIBILITY_LEVEL.

I need this to be dynamic sense different account have different budget so the CTE solution was the best until the issue with Lag.

Can anyone provide a method. Thanks

Pawan Kumar

Hi,
Please try this. I have removed the LAG code.

--

;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

--

Open in new window

Enjoy!

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sue Tippett

ASKER

Thank you for your support.