CODEWITHSUNDEEP
sqlsql-server
asmgx
asmgx

Reputation: 7984

How can I query the average over previous 12 months

I have a table that has monthly statistics

Calendar_Date   Month       Monthly Stat
1 Jun 16        Jun-16      1011    
1 Jul 16        Jul-16      1034            
1 Aug 16        Aug-16      1003            
1 Sep 16        Sep-16      991             
1 Oct 16        Oct-16      988             
1 Nov 16        Nov-16      999             
1 Dec 16        Dec-16      994             
1 Jan 17        Jan-17      981             
1 Feb 17        Feb-17      964             
1 Mar 17        Mar-17      966             
1 Apr 17        Apr-17      948             
1 May 17        May-17      942             
1 Jun 17        Jun-17      945             
1 Jul 17        Jul-17      938             
1 Aug 17        Aug-17      942             
1 Sep 17        Sep-17      941             
1 Oct 17        Oct-17      909             
1 Nov 17        Nov-17      911             
1 Dec 17        Dec-17      914             
1 Jan 18        Jan-18      929             
1 Feb 18        Feb-18      913

I want to find the annual statitcs that will be the previous 12 records So the stat for Dec-2017 will be the sum of 12 records before Dec-2017 which are Dec-2017 + Nov-17 + Oct-17 + ... + Jan-17 divided by 12

Calendar_Date   Month       Monthly Stat    Yearly Stat
1 Jun 16        Jun-16      1011            -
1 Jul 16        Jul-16      1034            -
1 Aug 16        Aug-16      1003            -
1 Sep 16        Sep-16      991             -
1 Oct 16        Oct-16      988             -
1 Nov 16        Nov-16      999             -
1 Dec 16        Dec-16      994             -
1 Jan 17        Jan-17      981             -
1 Feb 17        Feb-17      964             -
1 Mar 17        Mar-17      966             -
1 Apr 17        Apr-17      948             -
1 May 17        May-17      942             985.08
1 Jun 17        Jun-17      945             979.58
1 Jul 17        Jul-17      938             971.58
1 Aug 17        Aug-17      942             966.50
1 Sep 17        Sep-17      941             962.33
1 Oct 17        Oct-17      909             955.75
1 Nov 17        Nov-17      911             948.33
1 Dec 17        Dec-17      914             941.75
1 Jan 18        Jan-18      929             937.42
1 Feb 18        Feb-18      913             933.17

I can do that by joining the table to itself 12 times 

SELECT *
FROM MyTable A
INNER JOIN  MyTable B ON DATEADD(M,-1,a.Calendar_Date) = b.Calendar_Date
INNER JOIN  MyTable C ON DATEADD(M,-2,a.Calendar_Date) = c.Calendar_Date
...
INNER JOIN  MyTable L ON DATEADD(M,-12,a.Calendar_Date) = l.Calendar_Date

which is not very practicle any better idea how to do that?

Upvotes: 2

Views: 3423

Answers (3)

Andrew
Andrew

Reputation: 1596

What you want is a windowing function.

SELECT Calendar_Date, Month, MonthlyStat, 
  IIF(ROW_NUMBER() OVER(ORDER BY Calendar_Date)>=12,
    ROUND(AVG(CAST(MonthlyStat AS DECIMAL))
      OVER (ORDER BY Calendar_Date ASC ROWS BETWEEN 11 PRECEDING AND CURRENT ROW),2),
    NULL) AS YearlyStat
FROM MyTable ORDER BY Calendar_Date

AVG(MonthlyStat) OVER (ORDER BY Calendar_Date ASC ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) takes the current row, and the 11 rows before it ordered by Calendar_Date, and give you the Average. We CAST MonthlyStat as decimal so the code doesn't return intergers, then ROUND to 2 decimal places as this is currency.

If you want for the 12 months before this month your ROWS clause in your windowing function becomes ROWS BETWEEN 12 PRECEDING AND 1 PRECEDING

IIF(ROW_NUMBER() OVER(ORDER BY Calendar_Date)>=12,[...],NULL) tests if this is the 12th or later row, and only calculates a yearly average for those rows where data exists for 12 months, returning null for rows 1-11. If you do change the ROWS clause, you also need to change this to skip one more row, just change >= to >.

SQLFiddle

Upvotes: 5

Blakey
Blakey

Reputation: 843

You should probably look at window functions to SUM the current and previous number of rows data then divide by 12

Something like:

SELECT 
  Calendar_Date
  , Month
  , Monthly_Stat
  , (SUM(Monthly_Stat) OVER (ORDER BY Calendar_Date ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)) / 12 AS Yearly_Stat
FROM MyTable

More info in docs.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69504

SELECT *
  , AVG(MonthlyStat) OVER (ORDER BY CAST(REPLACE(Calendar_Date,' ','') AS DATE) ASC 
                         ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS YearlyStat
FROM YourTable

Upvotes: 1

Related Questions