June 15, 2016 at 2:22 am
hi,
I am trying to return a rolling 12 months value, using the following:
;WITH CTE_Rolling AS (
SELECT
[ReportDate]
,[Department]
,[SicknessDaysLost]
FROM
)
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?
June 15, 2016 at 3:40 am
Well, it seems to work when I use FOLLOWING.. but obviously that's not what I need 🙁
June 15, 2016 at 3:53 am
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