CODEWITHSUNDEEP
sqlsql-server
maylin safrini
maylin safrini

Reputation: 3

I want to find an average value based on a 12-month interval backwards

I want to find the average of the value column based on the period column 12 months backwards. How do you apply it to SQL?

(column data consists of months and years which are worth nchar (6))

please help for experts, thank you.

enter image description here

Upvotes: 0

Views: 53

Answers (3)

EzLo
EzLo

Reputation: 14189

Assuming you have all this on a table and not on Excel and Period is an INT, you can use the following average window function to calculate the running average on last year, for each row:

SELECT
    T.*
    AverageOverLastYear = AVG(T.Value) OVER (
        ORDER BY 
            T.Period ASC 
        ROWS 
            BETWEEN 11 PRECEDING AND CURRENT ROW)
FROM
    YourTable AS T

This also assumes that you have a record for each month because it will do the average with the previous rows, not considering if they are actually the previous month or not. If you might have gaps, then either consider inserting the value with NULL or using another approach with dynamic groupings.

Upvotes: 0

Esteban P.
Esteban P.

Reputation: 2809

This could be easily done in SQL server with window-functions.

Have a look here: Use window functions in SQL server

Especially the Value Window Functions LAG() and LEAD() are doing exactly what you're looking for.

Upvotes: 0

Cato
Cato

Reputation: 3701

  SELECT AVG(VALUE) AS AV 
                    FROM YourTab 
                    WHERE 
                            PERIODE BETWEEN 
                                cast(YEAR(dateadd(month,-11,CAST(PERIODE + '01' as datetime))) * 100 
                                        + MONTH(CAST(PERIODE + '01' as datetime)) as nchar(6))   --go back 11 months for example
                                AND
                                cast(YEAR(dateadd(month,0,CAST(PERIODE + '01' as datetime))) * 100 
                                + MONTH(CAST(PERIODE + '01' as datetime))  as nchar(6))    --go back 0 months, for example
            GROUP BY PERIODE

Upvotes: 1

Related Questions