How to create a weekly report query for the previous 8 weeks in SQL Server 2008

Data and Databases
1

I’m working with a sales database that stores transaction records with timestamp fields. I already have a working query that pulls data between two specific dates, but now I need to modify it to generate weekly reports.

I want to create a query that automatically calculates date ranges for the past 8 weeks. Each week should run from Monday through Sunday. The first result set should cover this current week (Monday to Sunday), then the second should cover last week (Monday to Sunday), and so forth.

My current approach involves running separate queries and then combining the results. Here’s what I’m working with:

SELECT 
    [TransactionDate],
    COUNT(*) AS WeeklyCount
FROM [SalesData]
WHERE Transaction_Date BETWEEN '2013/06/10' AND '2013/06/16'
GROUP BY CompanyID

I need to run this same query structure 8 times with different date ranges. Is there a way to dynamically calculate the Monday and Sunday dates for each of the past 8 weeks? Any suggestions would be helpful.

2

Use a recursive CTE with date arithmetic to generate weekly ranges automatically. Start by finding Monday of the current week - subtract days based on DATEPART(WEEKDAY, GETDATE()). Then let the CTE generate eight iterations, one for each week back. Join this CTE to your sales data where transaction dates fall between each week’s Monday and Sunday. No need for multiple separate queries since it handles date calculations automatically. UNION ALL gives you exactly eight weeks with proper Monday-to-Sunday boundaries.

3

hey alex! that’s an interesting challenge. you might wanna use DATEPART and DATEDIFF to get those monday/sunday ranges. also, just curious, when you say “current week” are you referring to this week or last? it could change up your date calculations.