How to calculate rolling 12 months average for 3 years

Hi,

I am trying to calculate rolling 12 months average for 3 years for every ID and my result is not what i am expecting.

Below is the example of my dataset.

--drop table #table1
create table #table1(
ID int,
MonthYear varchar(50),
netvalue_1 float,
netvalue_2 float,
netvalue_3 float
)

insert into #table1
select 1,'006.2021','8000.56','1001.00','1002.00'
UNION ALL
select 1,'009.2023','2345.67','1010.00','1003.00'
UNION ALL
select 1,'004.2024','2367.78','1100.00','2000.00'
UNION ALL
select 1,'006.2024','1234.56','3000.00','1010.00'
UNION ALL
select 2,'010.2023','3478.56','4000.00','2000.00'
UNION ALL
select 2,'001.2024','8947.56','2500.00','4500.00'
UNION ALL
select 2,'001.2021','3000.56','2590.00','4560.00'

My code is:

select ID,MonthYear,
AVG(netvalue_1) OVER (ORDER BY MonthYear ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ,
AVG(netvalue_2) OVER (ORDER BY MonthYear ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) ,
AVG(netvalue_3) OVER (ORDER BY MonthYear ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
from #table1
order by ID,MonthYear

firstly, Monthyear is not sorting and avg is not been calculating correctly.

Can anyone please let me know what I am missing here?

You are storing a Year and Month in a varchar column. And it is month first and the year. When you are `ORDER BY` that column, it is month first and then year.

You should store Year and Month it it's separate integer column

Storing the year and month in an a varchar column might cause sorting issues especially when the month goes first. It is far better to keep them in their own integer columns, which allows for more precise sorting and querying. It will greatly ease data leadership!