There is still some that is not clear. Assuming your weeks start on Sunday at midnight, does this mean that you would want a calculation run today to start with 20140525 at midnight, and stop at 20150524 at midnight?

If so, then expressions like the ones below could be used:

SELECT

Last52FullWeekStart=dateadd(week,datediff(week,@@datefirst-1,getdate())-52,@@datefirst-1),

Last52FullWeekEnd=dateadd(week,datediff(week,@@datefirst-1,getdate()),@@datefirst-1)

It works by getting the difference in weeks between now and the first day in 1900 that matches the first day of week being used by the WEEK datepart function (something that can be changed with SET DATEFIRST), since you're using that for grouping.

In other words, if your weeks start with Sunday, it gets the difference in weeks between the first Sunday of 1900, and adds that difference minus 52 weeks to get the starting datetime, and adds that difference to get the ending datetime.

Using @@DATEFIRST like that makes it a little more robust, in case you're using a different day for the first day of the week than the US English default of Sunday (i.e., SET DATEFIRST 7).

Cheers!

EDIT: Actually, after I wrote this my brain started working again, and I remembered that DATEDIFF always uses Sundays to determine the difference in weeks, no matter what DATEFIRST is set to.

That means relying on DATEDIFF in WEEKs as above will sometimes include the ending partial week anyway when DATEFIRST is not 7. To get around that, we just have to get the difference in days and use integer math (divide by integer 7 and multiply by integer 7) to get days until the end of the last full week, as below:

SELECT

Last52FullWeekStart=dateadd(day,((datediff(DAY,@@datefirst-1,getdate())/7)-52)*7,@@datefirst-1),

Last52FullWeekEnd=dateadd(day,(datediff(DAY,@@datefirst-1,getdate())/7)*7,@@datefirst-1)

One of these days I'll remember everything and not make such silly mistakes (I know it's not true, but that's what I tell myself). Until then... :blush: