How to calculate rolling 12 months average for 3 years

Transact-SQL
1

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?

2

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

3

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!

4 
Hi 

Hope this helps 


 -- `Preformatted text`Create the temporary table
CREATE TABLE #table1
             (
                          id        INT,
                          monthyear VARCHAR(50),
                          netvalue_1 FLOAT,
                          netvalue_2 FLOAT,
                          netvalue_3 FLOAT
             );

-- Insert sample dataINSERT 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;

-- Calculate rolling 12 months average
SELECT   id,
         monthyear,
         avg(netvalue_1) OVER (partition BY id ORDER BY try_cast(monthyear as date) rows BETWEEN 11 PRECEDING AND      CURRENT row) AS rollingavg_netvalue_1,
         avg(netvalue_2) OVER (partition BY id ORDER BY try_cast(monthyear AS date) rows BETWEEN 11 PRECEDING AND      CURRENT row) AS rollingavg_netvalue_2,
         avg(netvalue_3) OVER (partition BY id ORDER BY try_cast(monthyear AS date) rows BETWEEN 11 PRECEDING AND      CURRENT row) AS rollingavg_netvalue_3
FROM     #table1
ORDER BY id,
         try_cast(monthyear AS date);