Today, on the Interwebs, I found a great way to group data into ranges. I modified it quite a bit to fit my needs, however, I imagine you could apply this to any type of ranges you might want to use.

In this case, I wanted to group a dataset of information into time ranges (4 hour groups).

Essentially, I create a temporary dimensional table and join to it. Simply brilliant! I’m ashamed to admit I’ve never used these types of dimensional temp tables in my queries. However, I’m learning it won’t be my last.

DECLARE @Time_Ranges TABLE (
RangeName varchar(18) UNIQUE,
Minimum int UNIQUE,
Maximum int UNIQUE
)

INSERT INTO @Time_Ranges VALUES ('12:00 AM - 3:00 AM',0,3)
INSERT INTO @Time_Ranges VALUES ('4:00 AM - 7:59 AM',4,7)
INSERT INTO @Time_Ranges VALUES ('8:00 AM - 11:59 AM',8,11)
INSERT INTO @Time_Ranges VALUES ('12:00 PM - 3:59 PM',12,15)
INSERT INTO @Time_Ranges VALUES ('4:00 PM - 7:59 PM',16,19)
INSERT INTO @Time_Ranges VALUES ('8:00 PM - 11:59 PM',20,23)

SELECT RangeName, COUNT(*) AS Total
FROM [YOURSQLTABLE] INNER JOIN
@Time_Ranges ON DATEPART(hour, DateTimeField) >= Minimum AND
DATEPART(hour, DateTimeField) <= Maximum
GROUP BY RangeName, Minimum
ORDER BY Minimum