question

QHarris avatar image
QHarris asked

Recursive CTE over partitions.Detail in code.

Hi, I have a recursive CTE problem (detailed fully in the code below with source and expected results tables). Essentially, i am trying to apply a formula over partitions that includes using a constant and taking row above values (from second row onwards) of each partition. Any help gratefully received. I should also state I am running on: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) I include a section of code with a CTE that shows you what i am trying to achieve within each partition. Thanks, Quentin --RECURSIVE CTE PROBLEM OVER PARTITIONS TO POPULATE LATER ROWS USING PRIOR ROWS VALUES AND A CONSTANT --COLUMNS A&C FORM PARTITIONS I.E. ONE PARTITION FOR EACH UNIQUE COMBINATION OF A&C USE TEMPDB; GO DECLARE @CONSTANT DECIMAL (18,2) = .8; IF OBJECT_ID(N'tempdb..#SOURCEDATA',N'U') IS NOT NULL DROP TABLE #SOURCEDATA; CREATE TABLE #SOURCEDATA(PROV VARCHAR(150),B DATE ,METRIC VARCHAR(150), X SMALLINT, Y REAL, R REAL); INSERT #SOURCEDATA(PROV,METRIC, X,Y,R) VALUES ('PROV_A','M1',1,2,NULL), ('PROV_A','M1',2,3,NULL), ('PROV_A','M1',3,4,NULL), ('PROV_A','M1',4,6,NULL), ('PROV_A','M1',5,5,NULL), ('PROV_A','M2',1,12,NULL), ('PROV_A','M2',2,14,NULL), ('PROV_A','M2',3,33,NULL), ('PROV_A','M2',4,43,NULL), ('PROV_A','M2',5,34,NULL), ('PROV_B','M1',1,23,NULL), ('PROV_B','M1',2,12,NULL), ('PROV_B','M1',3,15,NULL), ('PROV_B','M1',4,16,NULL), ('PROV_B','M1',5,23,NULL), ('PROV_B','M2',1,45,NULL), ('PROV_B','M2',2,43,NULL), ('PROV_B','M2',3,56,NULL), ('PROV_B','M2',4,35,NULL), ('PROV_B','M2',5,23,NULL); SELECT * FROM #SOURCEDATA; --R VALUES (WITHIN EACH PARTITION) ARE CALCULATED AS FOLLOWS: -- R WHERE X=2 TAKES VALUE OF Y WHERE X=1 (i.e. 2) --FROM THEN ON EACH R IS THE PREVIOUS ROW Y *@CONSTANT + PREVIOUS ROW R*(1-@CONSTANT) -------------------------------------------------------- --REQUIRED CTE CODE TO POPULATE COLUMN R FOR X NUMBER OF ROWS -------------------------------------------------------- --RESULTS WOULD BE AS FOLLOWS: IF OBJECT_ID(N'tempdb..#RESULTS',N'U') IS NOT NULL DROP TABLE #RESULTS; CREATE TABLE #RESULTS(PROV VARCHAR(150),B DATE ,METRIC VARCHAR(150), X SMALLINT, Y REAL, R REAL); INSERT #RESULTS(PROV, METRIC, X,Y,R) VALUES ('PROV_A','M1',1,2,NULL), ('PROV_A','M1',2,3,2), ('PROV_A','M1',3,4,2.8), ('PROV_A','M1',4,6,3.76), ('PROV_A','M1',5,5,5.552), ('PROV_A','M2',1,12,NULL), ('PROV_A','M2',2,14,12), ('PROV_A','M2',3,33,13.6), ('PROV_A','M2',4,43,29.12), ('PROV_A','M2',5,34,40.224), ('PROV_B','M1',1,23,NULL), ('PROV_B','M1',2,12,23), ('PROV_B','M1',3,15,14.2), ('PROV_B','M1',4,16,14.84), ('PROV_B','M1',5,23,15.768), ('PROV_B','M2',1,45,NULL), ('PROV_B','M2',2,43,45), ('PROV_B','M2',3,56,43.4), ('PROV_B','M2',4,35,53.48), ('PROV_B','M2',5,23,38.696); SELECT * FROM #RESULTS; --N.B. NUMBER OF ROWS CAN VARY
ctepartitioningrecursion
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Try this.. with recursive_cte as ( select PROV , METRIC , X , Y , R from #SOURCEDATA where X=1 union all select sd.PROV , sd.METRIC , sd.X , sd.Y , case when sd.X = 2 then rc.Y else (rc.Y*@CONSTANT) + rc.R*(1-@CONSTANT) end as R from #SOURCEDATA sd join recursive_cte rc on sd.X = rc.X+1 and sd.PROV = rc.PROV and sd.METRIC = rc.METRIC ) select * from recursive_cte order by recursive_cte.PROV, recursive_cte.METRIC, X
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I bow my head to you Kev Riley!!! Awesome. I had tried the following but keep getting rounding issues in result. Is it because of a datatype conversion? - ;WITH getRs AS ( SELECT x,y, CONVERT(REAL,NULL) AS R, CONVERT(REAL,NULL) AS R2 FROM #sourcedata WHERE x = 1 -- this is the starting point you want in your recursion UNION all SELECT c.x,c.y,CONVERT(REAL,(p.y)),CONVERT(REAL,(p.R)) FROM #sourcedata c JOIN getRs p ON p.x+1 = c.x -- this is the recursion ) INSERT #Rec_Results SELECT * FROM getRs --where x >2 OPTION (maxrecursion 0) SELECT x, y, @Alpha, (R*@Alpha)+(R2*(1.00-@Alpha))Forecast FROM #Rec_Results WHERE x>2
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.