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.
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
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.