Getting a rolling 12 months value

  • hi,

    I am trying to return a rolling 12 months value, using the following:

    ;WITH CTE_Rolling AS (

    SELECT

    [ReportDate]

    ,[Department]

    ,[SicknessDaysLost]

    FROM


    .[SicknessDaysLost]

    )

    SELECT ReportDate, [SicknessDaysLost]

    , SUM([SicknessDaysLost]) OVER (PARTITION BY Department ORDER BY ReportDate ROWS BETWEEN CURRENT ROW AND 12 PRECEDING) AS RollingYearDaysLost

    FROM CTE_Rolling

    GROUP BY

    ReportDate, [SicknessDaysLost],Department

    ORDER BY ReportDate

    However, I am getting the error:

    'BETWEEN CURRENT ROW AND ... PRECEDING' is not a valid window frame and cannot be used with the OVER clause.

    The ReportDate values are the first of each month, and I have about 2 years worth of data, so I am not sure what the problem is. Can anyone see where I am going wrong please?

  • Well, it seems to work when I use FOLLOWING.. but obviously that's not what I need 🙁

  • I fixed it...

    IT matters the order you put the ROWS clause in. Switching to

    BETWEEN 12 PRECEEDING AND CURRENT ROW

    mad it work.

    Must be the way it spools the data?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply