title: Using recursion with a CTE in SQL Server
body: <p>I have following table Structure: (this is just a sample set with exact same columns in my final output query)</p>
  <p>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.</p>
  <p><a href="https://dbfiddle.uk/?rdbms=sqlserver_2016&amp;fiddle=b1d5ed7db79c665d8cc179ae4cc7d4f1" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=sqlserver_2016&amp;fiddle=b1d5ed7db79c665d8cc179ae4cc7d4f1</a></p>
  <p>This is link to the fiddle for SQL data
  below is the image of the same:</p>
  <p><a href="https://i.stack.imgur.com/rU2xq.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/rU2xq.png" alt="enter image description here" /></a></p>
  <p>I want to calculate point contribution to the index value and finally the index value.</p>
  <p>To calculate <code>pts</code> contribution by each symbol the formula is :</p>
  <pre><code>ptsC = yesterday_index * wt * px_change / yest_close
  </code></pre>
  <p>I do not have beginning value of yesterday Index .i.e for 17 Nov 2021 and should be considered as 1000</p>
  <p>The Index Value of 18 Nov will then be <code>1000 + sum(ptsC)</code>
  This value should now be used to calculate ptsC for each symbol for 22-Nov and so on...</p>
  <p>I am trying to write a recursive CTE but am not sure where I am going wrong.</p>
  <p>Yesterday Index value should be recursively determined and thus the ptsC should be calculated.
  The final output should be:</p>
  <p><a href="https://i.stack.imgur.com/DLuES.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DLuES.png" alt="enter image description here" /></a></p>
  <p>where total Point Contribution is sum of all the <code>ptsC</code> for the day and New index Value is <code>yesterday Index Value + Total Point Contribution</code>.</p>
  <p>Below is the code I have which generates the first table:</p>
  <pre><code>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 &lt;='2021-11-24'
  order by datetime
  </code></pre>
  <p>Thanks in advance.</p>