# SQL Server: Help in writing a query to get 3 rolling months average for a table

Query Thus far:

``````select month
, year
, vol
--, sum(vol) over (order by month,year  ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
, avg(vol) over ( order by  month,year
ROWS BETWEEN 2 PRECEDING
AND current row) '3monthra'
, name
, cnt
, costx
from (
SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
, year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
, sum(SumOfNetAmount) as vol
, count(*) as cnt
, costx
, name
FROM [ngl_OTB]
GROUP BY costx
, MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
, YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
, name
, SumOfNetAmount
) s
where costx = '1000.002' and
name = 'ngl-raw'
order by month,year
``````

I need help on how to recursively set the [costx] and [name] columns to allow 3 months rolling avg of each [costx] and [name] correctly. All attempts provided wrong calculations like 3 months rolling avg was bleeding into other costx and name. My goal is to group all the data by costx and name and order by year and month, then performing a rolling 3 months avg on each costx using the value of [vol] for that [costx]

Any help will be greatly apppreciated

• It is better if you write an answer and accept it. You wont get any points for accepting your own answer but it can be upvoted by others (I will). Commented Feb 27, 2020 at 4:30

``````select month
, year
, vol
--, sum(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) sumnetvol,
, avg(vol) over (partition by costx, name order by month,year ROWS BETWEEN 2 PRECEDING AND current row) '3monthra'
, name
, cnt
, costx
from (
SELECT month(convert(datetime, cast(monthnumber as nvarchar(10))+'01')) as [Month]
, year(convert(datetime,cast(monthnumber as nvarchar(10))+'01')) as [Year]
, sum(SumOfNetAmount) as vol
, count(*) as cnt
, costx
, name
FROM [ngl_OTB]
GROUP BY costx
, MONTH(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
, YEAR(convert(datetime,cast(monthnumber as nvarchar(10))+'01'))
, name
, SumOfNetAmount)s
where costx = '1000.002' and name = 'ngl-raw' order by month,year
``````