Grouping Events by Date Ranges In SQL Server

February 09, 2018

Let’s say you have a table of events. Each event has a beginning and an ending date. How would you get a list of the events that happened within each quarter?

Example: A event that begins on 2-1-2017 and ends on 8-1-2017 would have an entries for Q1 2017, Q2 2017, and Q3 2017.

If you’re dealing with a single value, you can just use DATEPART(quarter, [DateValue]), but if you’re looking to figure out if an event occurs in a range, it’s a little more complicated. Here’s how to do it.

Getting Date Ranges

The first step to getting this data is to get a list of quarters with their respective date ranges. One way to do this is to use a recursive common table expression. CTE's are snippets of SQL that you can put above your main query to create tables to join against. A recursive CTE creates values by calling itself. Here's a CTE that'll give us a list of quarters from the start date to the current date. When using recursive CTEs, make sure you have a termination condition or you'll have a nice infinite loop on your hands.
DECLARE @start Date = '01-01-2010';
DECLARE @End Date = GETDATE();

WITH Quarters_CTE
AS
(
SELECT @start as [Start], DateAdd(quarter, 1, @start) AS [End], DATEPART(quarter, @start) AS [Quarter], DATEPART(year, @start) AS [Year]
UNION ALL
SELECT DateAdd(quarter, 1, [Start]), DateAdd(quarter, 1, [End]), DATEPART(quarter, DateAdd(quarter, 1, [Start])), DATEPART(year, DateAdd(quarter, 1, [Start]))
FROM Quarters_CTE
WHERE [End] < @end
)
SELECT * FROM Quarters_CTE Q

Matching Quarters To Events

The next step is compare our events with the quarters. We want a different entry for each quarter the event occurs in. To do this you can join up with the table. You can use non-equality based comparison operators in join queries, so we filter by date range. Here's the complete query:
DECLARE @start Date = '01-01-2010';
DECLARE @End Date = GETDATE();

WITH Quarters_CTE
AS
(
SELECT @start as [Start], DateAdd(quarter, 1, @start) AS [End], DATEPART(quarter, @start) AS [Quarter], DATEPART(year, @start) AS [Year]
UNION ALL
SELECT DateAdd(quarter, 1, [Start]), DateAdd(quarter, 1, [End]), DATEPART(quarter, DateAdd(quarter, 1, [Start])), DATEPART(year, DateAdd(quarter, 1, [Start]))
FROM Quarters_CTE
WHERE [End] < @end
)
SELECT * FROM Quarters_CTE Q
JOIN [Events] E ON e.StartDate < q.[End] AND (e.EndDate is null OR e.EndDate > q.[Start])

From there you can aggregate your data as needed.


Dustin Ewers

Written by Dustin Ewers who lives and works in Southern Wisconsin. GitHub | Twitter | LinkedIn


© 2020, Built by Dustin Ewers with Gatsby
Disclaimer

The posts on this site are my own and don’t necessarily represent my employer's positions, strategies, or opinions.

Some of the links contained within this site have my referral ID, which provides me with a small commission for each sale. Thank you for your support.