Using recursion with a CTE in SQL Server

Question

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.

https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b1d5ed7db79c665d8cc179ae4cc7d4f1

This is link to the fiddle for SQL data below is the image of the same:

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.

Answer 1

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