Previous Monday to the Start of the Previous Quarter
So, we need to go back to the start of the previous quarter but need to provide full weeks of data based on a Monday as the start of the week.
I’ve googled loads of different ways to do this, lots using @@DateFirst, datepart(dw) etc..
After a lot of confusion, I came up with this gem. In simply grandad terms, some date functions work from an integer where 0 = 1900-01-01 00:00:00.0000
SELECT DATEADD(MI,30,0) = 1900-01-01 00:30:00.000
So, we need code to go back to the previous quarter, but we need it to be the start of the previous quarter. What the photon, does that statement mean ? Well if today is the 18th of the month and I code
SELECT DATEADD(QQ, -1, GETDATE())
I will get the 18th from 3 months ago. As last time I checked we have 12 months and a quarter was 3 months. My memory is still good !!
So here we go again, with using the zero (1900-01-01) as the start point. We can determine the number of quarters difference between zero and today. Then add the number of quarters -1 to the zero date, which will give us the start of the previous quarter.
The next bit, is similar to quarters, where we determine the number of weeks between zero and the start of the previous quarter, then add them to 1900-01-01 and voila, you now have the Monday (start of the week) prior to the start of the previous quarter. Job done, time for a cuppa.
Change the -1 to -2 to go back 2 quarters etc.
SELECT DATEADD(QQ, -2, GetDate()) as PreviousXXQuarters, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) -2, 0) as BeginingOfQuarter, --beginning of quarter. DATEADD(wk,DATEDIFF(wk,0,DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) -2, 0)),0) as PreviousMondayBeforeStartOfQuarter