Hi,
So I'm looking at activity for the last 8 weeks and I want to compare it to the equivalent 8 weeks the previous year (Day of the week is important as it will make a difference to the data!)
So far, I have managed to do the below to obtain last 8 weeks activity (from yesterday)
WHERE DATEDIFF( d, ActivityDate, GETDATE() ) < 57
This would provide me with a total Activity for Thursday 14/04/16 - Wednesday 08/06/16
I need to be able to pull through the same time period for last year
(Thursday 16/04/15 - Wednesday 10/06/15)
Please help 
Simple date calcs can get you back to the same day of the week last year. I used CROSS APPLYs to provide alias names for intermediate calculation values so the main SELECT code is less cluttered.
Once the number of weeks parameter value is set as you need it, the current and prior year dates are calculated automatically based off the current date (GETDATE()).
DECLARE @num_of_weeks_of_activity int
SET @num_of_weeks_of_activity = 7
SELECT ...whatever...
FROM table_name
CROSS APPLY (
SELECT DATEADD(DAY, -@num_of_weeks_of_activity * 7, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS this_years_first_date_to_pull
) AS ca1
CROSS APPLY (
SELECT DATEDIFF(DAY, 0, this_years_first_date_to_pull) % 7 AS this_years_first_date_day_of_week
) AS ca2
CROSS APPLY (
SELECT DATEADD(DAY, -DATEDIFF(DAY, this_years_first_date_day_of_week, last_years_initial_date_to_pull) % 7,
last_years_initial_date_to_pull) AS last_years_first_date_to_pull
FROM (
--backup up 1 year *less 6 days* so that we can then move backward to the same *day of the week* as last years date.
SELECT DATEADD(DAY, 6, DATEADD(YEAR, -1, this_years_first_date_to_pull)) AS last_years_initial_date_to_pull
) AS initial_date
) AS ca3
WHERE
((ActivityDate >= this_years_first_date_to_pull AND
ActivityDate < DATEADD(DAY, @num_of_weeks_of_activity * 7 + 1, this_years_first_date_to_pull))
OR
(ActivityDate >= last_years_first_date_to_pull AND
ActivityDate < DATEADD(DAY, @num_of_weeks_of_activity * 7 + 1, last_years_first_date_to_pull)))
That's great - Thank you.
Could I just check - If I change the week to 8 - from what date does it count back 56 days?
If I needed to look at a specific date / date range (and not yesterday)
Where were would I put it, so that it looks at the date and then recalculates to the same time last year?
From the current date back, that is, 56 days back from today's date, whatever that is.
Change "GETDATE()" in the initial date calc to be whatever date you want. That's why I wrote the calc that way -- so that you could easily change the "base" date.