How to aggregate data in SQL into a format of every two weeks (showing date of beginning of first week)

Question

What is the best way to aggregate data in SQL so that it's broken up into two week periods. So say for a given three month period (which is what I'm trying to solve here) I work for a company that sells produce and they sell cases of tomatoes. I need to show for every two weeks how many tomatoes a customer has purchased. If this was weekly I could aggregate the results using one of the below methods... cast(DATEADD(ww, DATEDIFF(ww, 0, min(h.document_date)), -1) as date) OR DATEPART(ww,cast(h.document_date as date))

Either of the above methods would give me weekly results, but i need the results of two weeks worth where the date showing is the beginnning of the two week period. So, for example if I was running this for April 2019 the first row of the results would show '3/31/2019' and would have the cases purchased through the 13th of april (since that's two weeks worth of cases). The next row would then have a date of '4/14/2019'. I hope this makes sense.

I have solved this issue by using a work around but I'm still unsure how someone would aggregate a query for every two weeks.

Answer 1

taking your example

DATEPART(ww,cast(h.document_date as date))

could you make all the odd week to be the previous even week?

floor(DATEPART(ww,cast(h.document_date as date))/2)*2

Alternatively, if you want the actual start of the date. Find the difference in days between the first day of 2 weeks period and your date, then subtract from it

Try this :

DATEADD(day,  -DATEDIFF( day , '2019-03-31' , OrderDate) % 14, OrderDate)

Replace the 2019-03-31 with 2019-04-07 if you want to start your 2 week period with different Sunday.