05 June 2017

Partition by Half-a-year using windows functions

Let's say you're using Windows functions, and you want to partition by a quarter.
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