'Rolling 12 month Calculation SQL

I am trying to do a 12 month rolling calculation, but I get a syntax error at "rows", here is what I have so far:

(SUM(YTDValue) OVER (ORDER BY PerformanceDate ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)) / 12 AS Yearly_YTDValue
sqlsql-server


Solution 1:[1]

It might be your RDMBS doesn't support that syntax, because at first glance it looks correct to me.

This method will only work if you are guaranteed to have exactly 12 PerformanceDates, so sometimes I prefer this method because it does not require me to aggregate the data to monthly levels first.

WITH BASIC_OFFSET_7DAY AS (
  SELECT 
    A.DATE, 
    SUM(B.YTDValue) as Yearly_YTDValue
  FROM 
    UserActivity A 
    JOIN UserActivity B
  WHERE 
    B.DATE >= DATEADD(year, -12, A.DATE) 
    AND B.DATE <= A.DATE 
  GROUP BY A.DATE
) 
SELECT 
  src.*, 
  BASIC_OFFSET_7DAY.Yearly_YTDValue 
FROM 
  UserActivity src 
  LEFT OUTER JOIN BASIC_OFFSET_7DAY ON BASIC_OFFSET_7DAY.DATE = src.DATE

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Josh

Related Questions