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.

Rolling Average SQL

How many scripts you need to see for rolling average (AVG) in SQL? Well, it never hurts to see another one...
This is the beauty of Windows Functions, they take a complicated problem and put it in a simple script.

(BTW - I've taken the Sales numbers from here - a great webiste, but I believe it's for PostgresSQL, and then added the year 2016 and the extra numbers. I believe you'll be able to find the pattern.)

CREATE TABLE #temp  (StartDate DATE, Sales INT)

INSERT #temp (StartDate, Sales)
VALUES ('20150110', 5)
,('20150210', 3)
,('20150310', 7)
,('20150410', 8)
,('20150510', 2)
,('20150610', 3)
,('20150710', 6)
,('20160110', 8)
,('20160210', 6)
,('20160310', 10)
,('20160410', 11)
,('20160510', 5)
,('20160610', 6)
,('20160710', 9)



If you just want the rolling average (you can replace the AVG function with SUM, MIN, MAX as well) for all the time periods, you need to run the following code:


SELECT
StartDate,
AVG (sales) OVER ( order by StartDate ) AS AVGSales
FROM #temp


But if you want to partition by YEAR, for example, you'd better run the following code:


SELECT
StartDate,
AVG (sales) OVER (Partition  by YEAR (StartDAte) order by StartDate ) AS AVGSales
FROM #temp



Short & sweet!

(this is a simple example; I need to run this on a total assets for a fund. It's amazing how those extra digits get us confused!)

Don't forget :)

DROP TABLE #temp