Recursive CTE to replace a cursor problem

Question

Recursive CTE to replace a cursor problem

Robb Melancon

Ten Centuries

Points: 1129
More actions
July 20, 2010 at 3:27 pm

#226981

I am trying to use recursive CTEs to replace cursors and I am having trouble figuring out how to "reset" certain fields when a "key" field changes. In the example provided I am calculating performance for stocks and I have a recursive CTE that works for a single stock. (see results of the first CTE for 'IBM' in the example). The problem comes when I have more than one stock I can't figure out how to "reset" performance when the "loop" changes from one Asset/Stock to the next. So when I add the rows for the second stock (T), the way I have it coded it won't work because I am joining simply on sequence where I need to have the AssetID in there as sequences are repeated for each stock and need to reset running performance to 0 when the asset changes. I'm just not sure how to add the assetID in the joins and have it repeat for each stock. I'm thinking an "outer" recursive CTE that loops the AssetIDs for the "inner" CTE that calculates the performance but I am having trouble figuring how that would work. Hoping a recusive CTE expert can help out.
Thanks in advance,
Robb
Here's the code and example:
create table #DMVRunningPerformance (
Seq int,
LongPosition_bt bit null,
AssetID_in int null,
Symbol_vc varchar(255),
DayID_in int,
Status_vc varchar(255) null,
MV float,
DailyPerf float null,
RunningPerf float null)
CREATE CLUSTERED INDEX [indexdown] ON #DMVRunningPerformance
(
[Seq] ASC,
[LongPosition_bt] ASC,
[AssetID_in] ASC,
[DayID_in] DESC
)
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
-- insert first Asset
insert into #DMVRunningPerformance
values (1,1,100,'IBM',75006,NULL,201048.8987,NULL,NULL)
insert into #DMVRunningPerformance
values (2,1,100,'IBM',75005,NULL,200841.5658,NULL,NULL)
insert into #DMVRunningPerformance
values (3,1,100,'IBM',75004,NULL,200321.7043,NULL,NULL)
insert into #DMVRunningPerformance
values (4,1,100,'IBM',75003,NULL,201120.0467,NULL,NULL)
insert into #DMVRunningPerformance
values (5,1,100,'IBM',75002,NULL,201779.8805,NULL,NULL)
insert into #DMVRunningPerformance
values (6,1,100,'IBM',75001,NULL,201651.3917,NULL,NULL)
insert into #DMVRunningPerformance
values (7,1,100,'IBM',75000,NULL,201651.3917,NULL,NULL)
declare @AssetID_in int=-1,
@NDAssetID_in int=-1,
@LongPosition_bt bit=0,
@Status_vc varchar(255)=NULL,
@NewGroup_bt bit=1,
@DailyPerformance float,
@RunningPerformance float,
@NDMarketValue float
;WITH Calculator AS (
-- Anchor row, tr.Seq = 1
SELECT thisrow.Seq, thisrow.Symbol_vc, thisrow.DayID_in, thisrow.MV,
DailyPerf=CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT),
RunningPerf = CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT)
FROM #DMVRunningPerformance thisrow
INNER JOIN #DMVRunningPerformance nextrow ON nextrow.Seq = thisrow.Seq + 1
WHERE thisrow.Seq = 1
UNION ALL
-- first recursion will be Seq = 2 (thisrow.Seq = 2, lastrow.Seq = 1)
SELECT thisrow.Seq, thisrow.Symbol_vc, thisrow.DayID_in, thisrow.MV,
DailyPerf = CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT),
RunningPerf = ((1 + lastrow.RunningPerf) * (1 + CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT))) - 1
FROM Calculator lastrow
INNER JOIN #DMVRunningPerformance thisrow ON thisrow.Seq = lastrow.Seq + 1
INNER JOIN #DMVRunningPerformance nextrow ON nextrow.Seq = thisrow.Seq + 1
)
SELECT Seq, Symbol_vc, DayID_in, MV, DailyPerf, RunningPerf
FROM Calculator
-- insert second Asset
insert into #DMVRunningPerformance
values (1,1,200,'T',75006,NULL,6484,NULL,NULL)
insert into #DMVRunningPerformance
values (2,1,200,'T',75005,NULL,6450,NULL,NULL)
insert into #DMVRunningPerformance
values (3,1,200,'T',75004,NULL,6398,NULL,NULL)
insert into #DMVRunningPerformance
values (4,1,200,'T',75003,NULL,6397,NULL,NULL)
insert into #DMVRunningPerformance
values (5,1,200,'T',75002,NULL,6446,NULL,NULL)
insert into #DMVRunningPerformance
values (6,1,200,'T',75001,NULL,6240,NULL,NULL)
insert into #DMVRunningPerformance
values (7,1,200,'T',75000,NULL,6124,NULL,NULL)
-- this obviously will not work as it needs to work on one assetid at a time
-- so how do I "reset" the sequence back to 1 and the running performance back to 0 for each assetid?
;WITH Calculator AS (
-- Anchor row, tr.Seq = 1
SELECT thisrow.Seq, thisrow.Symbol_vc, thisrow.DayID_in, thisrow.MV,
DailyPerf=CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT),
RunningPerf = CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT)
FROM #DMVRunningPerformance thisrow
INNER JOIN #DMVRunningPerformance nextrow ON nextrow.Seq = thisrow.Seq + 1
WHERE thisrow.Seq = 1
UNION ALL
-- first recursion will be Seq = 2 (thisrow.Seq = 2, lastrow.Seq = 1)
SELECT thisrow.Seq, thisrow.Symbol_vc, thisrow.DayID_in, thisrow.MV,
DailyPerf = CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT),
RunningPerf = ((1 + lastrow.RunningPerf) * (1 + CAST((thisrow.MV-nextrow.MV)/nextrow.MV AS FLOAT))) - 1
FROM Calculator lastrow
INNER JOIN #DMVRunningPerformance thisrow ON thisrow.Seq = lastrow.Seq + 1
INNER JOIN #DMVRunningPerformance nextrow ON nextrow.Seq = thisrow.Seq + 1
)
SELECT Seq, Symbol_vc, DayID_in, MV, DailyPerf, RunningPerf
FROM Calculator
drop table #dmvrunningperformance

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Robb Melancon Ten Centuries Points: 1129 More actions · Answer 1

Here is an example of a solution using an outer cursor with an inner recursive CTE. I'm trying to eliminate the outer cursor (and return it all as a single resultset)

create table #DMVRunningPerformance (

Seq int,

LongPosition_bt bit null,

AssetID_in int null,

Symbol_vc varchar(255),

DayID_in int,

Status_vc varchar(255) null,

MV float,

DailyPerf float null,

RunningPerf float null)

CREATE CLUSTERED INDEX [indexdown] ON #DMVRunningPerformance

(

[Seq] ASC,

[LongPosition_bt] ASC,

[AssetID_in] ASC,

[DayID_in] DESC

)

WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

-- insert first Asset