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 18^th of the month and I code

SELECT DATEADD(QQ, -1, GETDATE())

I will get the 18^th 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