question

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