Forum Discussion
Solved
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?
I made some change with your code, check the result, is that what your want?
SELECT ID, MonthYear, AVG(netvalue_1) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) , AVG(netvalue_2) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) , AVG(netvalue_3) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM #table1 ORDER BY ID, RIGHT(MonthYear, 4) + LEFT(MonthYear, 3)
2 Replies
I made some change with your code, check the result, is that what your want?
SELECT ID, MonthYear, AVG(netvalue_1) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) , AVG(netvalue_2) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) , AVG(netvalue_3) OVER (PARTITION BY ID ORDER BY RIGHT(MonthYear, 4) + LEFT(MonthYear, 3) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) FROM #table1 ORDER BY ID, RIGHT(MonthYear, 4) + LEFT(MonthYear, 3)- here avg works and also sorting. but i wanted 12 months rolling and it will be 11 preceding and current row
