Using recursion with a CTE in SQL Server-CodePudding

I have following table Structure: (this is just a sample set with exact same columns in my final output query)

Actual data has a much higher number of rows in index and I have to remove few symbols before arriving to the index value. This is a custom index to be built for internal use.

I want to calculate point contribution to the index value and finally the index value.

To calculate pts contribution by each symbol the formula is :

ptsC = yesterday_index * wt * px_change / yest_close

I do not have beginning value of yesterday Index .i.e for 17 Nov 2021 and should be considered as 1000

The Index Value of 18 Nov will then be 1000 sum(ptsC) This value should now be used to calculate ptsC for each symbol for 22-Nov and so on...

I am trying to write a recursive CTE but am not sure where I am going wrong.

Yesterday Index value should be recursively determined and thus the ptsC should be calculated. The final output should be:

where total Point Contribution is sum of all the ptsC for the day and New index Value is yesterday Index Value Total Point Contribution.

Below is the code I have which generates the first table:

declare @beginval as float=17671.65
set @beginval=1000
declare @indexname varchar(20)='NIFTY ENERGY'
declare @mindt as datetime
select @mindt=min(datetime) from indices_json where indexname=@indexname

;
with tbl as (
SELECT        IndexName, datetime, sum(Indexmcap_today) totalMcap_today,sum(Indexmcap_yst) totalmcap_yst
FROM            indices_json
WHERE        IndexName = @indexname
group by indexname,datetime
)
,tbl2 as
(
select j.indexname,j.datetime,symbol,Indexmcap_today/d.totalMcap_today*100 calc_wt_today,Indexmcap_yst/d.totalmcap_yst*100 calc_wt_yest,iislPtsChange,adjustedClosePrice,pointchange
from indices_json j inner join tbl d on d.datetime=j.datetime and d.IndexName=j.IndexName
)
,tbl3 as
(
select indexname,datetime,symbol,calc_wt_today,calc_wt_yest,iislPtsChange,adjustedClosePrice,pointchange
,case when datetime=@mindt then @beginval*calc_wt_yest*iislPtsChange/adjustedClosePrice/100 else null end ptsC
 from tbl2 
)
,tbl4 as
(
select indexname,datetime,sum(ptsC)   @beginval NewIndexVal,sum(pointchange) PTSCC 
from tbl3
group by indexname,datetime
)
,tbl5 as
(
select *,lag(datetime,1,null) over(order by datetime asc) yest_dt 
from tbl4 
)
,
tbl6 as 
(
select d.*,s.yest_dt 
from tbl2 d inner join tbl5 s on d.datetime=s.datetime
)
,tbl7 as
(
select d.IndexName,d.datetime,d.symbol,d.calc_wt_today,d.calc_wt_yest,d.iislPtsChange,d.adjustedClosePrice,d.pointchange,case when i.datetime is null then @beginval else i.NewIndexVal end yest_index 
from tbl6 d left join tbl4 i on d.yest_dt=i.datetime
)
select IndexName,convert(varchar(12),datetime,106)date,symbol,round(calc_wt_yest,4) wt,iislPtsChange px_change,adjustedClosePrice yest_close--,pointchange,yest_index
from tbl7 d where datetime <='2021-11-24'
order by datetime

Thanks in advance.

CodePudding user response：

I found a solution for this: I calculated the returns for each constituent for each date then summed up these returns for a date then multiplied all the sum of the returns of all dates to arrive at the final value - this works below is the query for the same. I did not require recursion here

declare @beginval as float=17671.65

declare @indexname varchar(20)='NIFTY 50'
declare @mindt as datetime
select @mindt=min(datetime) from indices_json where indexname=@indexname
declare @startdt as datetime = '2021-11-01'
;
with tbl as (
SELECT        IndexName, datetime, sum(Indexmcap_today) totalMcap_today,sum(Indexmcap_yst) totalmcap_yst
FROM            indices_json
WHERE        IndexName = @indexname-- and symbol!='AXISBANK'
group by indexname,datetime
)
,tbl2 as
(
select j.indexname,j.datetime,symbol,Indexmcap_today/d.totalMcap_today*100 calc_wt_today,Indexmcap_yst/d.totalmcap_yst*100 calc_wt_yest,iislPtsChange,adjustedClosePrice,pointchange
from indices_json j inner join tbl d on d.datetime=j.datetime and d.IndexName=j.IndexName
)
,tbl7 as
(
select d.IndexName,d.datetime,d.symbol,d.calc_wt_today,d.calc_wt_yest,d.iislPtsChange,d.adjustedClosePrice,d.pointchange, d.calc_wt_yest*d.iislPtsChange/d.adjustedClosePrice/100 ret
from tbl2 d 
)
,tbl8 as
(
select indexname,datetime,1 sum(ret) tot_ret from tbl7 group by indexname,datetime 
)

select indexname,datetime date
,round(exp(sum(log(sum(tot_ret))) over (partition by IndexName order by datetime)),6)*@beginval final_Ret
from tbl8 where datetime>=@startdt
 group by indexname,datetime order by date