Average of last 12 months data in SQL

Question

Hi All,

I need to dynamically calculate last 12 months of average values for current month.

Step1: I need to calculate 2021 May spend numbers based on last 12 months data.
Step2: Once derived May 2021 numbers ,Again need to take average of last 12 months data from 2021 May to 2019 June and those are final spend numbers for May 2021. I have similar data in year,month,State .
If my time period is P I need to calculate the average like above.

If time period is C ,depends on available data need to take simple average. Suppose I have the data only for 1 month, simply need to take that month, if 3 months data is available need to take average of values of 3 months for current month.

How to write the SQL for those cases?

Thanks,

CMK!

100614-photo-2021-05-27-15-41-59.jpg

Answer 1

SELECT a, b, AVG(value) OVER (PARTITION BY a, b
                              ORDER BY month
                              ROWS BETWEEN 12 PRECEDING AND CURRENT ROW)
FROM  tbl
``The caveat here is that if there is no data for a month, the query will go further back that you want. What you really want is RANGE BETWEEN, but this is not implemented in SQL Server.

By the way, for these type of questions, it is always a good idea to post CREATE TABLE statement for your table(s) and INSERT statements with sample data, and the desired result given the sample. That permits us to copy and paste into a query window to develop a tested query.