That's simple, you'd just do:
SELECT SUM ( [MyValue]) OVER (PARTITION BY [Employee], YEAR ([Valuation date]), DATEPART (qq,[Valuation date]) ORDER BY ROW_ID)
But what happens if you would like to run a sum by half a year? If it's just getting the value (first or second half of year), you'd play around with
SELECT CASE WHEN month < 7 THEN 1 ELSE 2 END AS [HalfAYear]
or with
SELECT CASE WHEN DATEPART (qq, Valuation date) < 3 THEN 1 ELSE 2 END AS [HalfAYear]
and that would do.
But since we need a running sum per half a year, you'd like to use this code:
SUM ( [MyValue]) OVER (PARTITION BY [Employee], YEAR ([Valuation date]), YEAR (DATEADD(m, 6, [Valuation date])) ORDER BY ROW_ID)
Using the YEAR for the first time gives me the current year, when I'm using YEAR over "add 6 month" I'm using the "Australian Financial Year", but this way I am partitioning by half a year. Please note that at the end of the day I don't know which half it is, but it doesn't matter, since I don't care about the label, I care about the results.
No comments:
Post a Comment