title: Calculate averages of previous 7 rows SQL
body: <p>Consider the following result set returned from a stored procedure:</p>
  
  <p><a href="https://i.stack.imgur.com/hp2AP.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/hp2AP.png" alt="enter image description here"></a></p>
  
  <p>The goal with the IHD column, is to do a calculation of the previous 6 rows (days) to determine a IHD value from within the stored procedure.</p>
  
  <p>In this case, only from row 7 and onwards will there be an IHD value, since the calculation needs to take into consideration the previous 6 days' closing balance including current day (day 7) and calculate an average. Basically, it needs to use row 1 to 7 for row's 7 IHD value. And then, to calculate row 8's IHD value, it needs to use row 2 to 8.</p>
  
  <p>I have had a look at <code>SQL LAG</code> function, but this only allows me to skip to 1 previous row, and I am not quite sure if I would be able to successfully use the <code>LAG</code> function in a self referencing <code>CTE</code> where averages of more than one previous row is required.</p>
  
  <p>How should I approach this scenario? </p>