CODEWITHSUNDEEP
sqlsql-serversql-server-2016
monstertjie_za
monstertjie_za

Reputation: 7803

Calculate averages of previous 7 rows SQL

Consider the following result set returned from a stored procedure:

enter image description here

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.

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.

I have had a look at SQL LAG 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 LAG function in a self referencing CTE where averages of more than one previous row is required.

How should I approach this scenario?

Upvotes: 1

Views: 403

Answers (1)

Thom A
Thom A

Reputation: 95559

Use ROWS BETWEEN. Without Consumable sample data and expected results I can only give Pseudo SQL, but this'll put you on the right path:

AVG({Your Column}) OVER ([PARTITION BY {Other Column}] ORDER BY {Column To Order BY}
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

Obviously replace the parts in braces ({}) and remove the parts in brackets ([]) if not required.

Upvotes: 6

Related Questions