SQL Server: Group By Days and Create Categories

In this post, we will see how to Group records by Days in SQL Server and then create categories. To demonstrate, we will take a sample Student-Course table where the start date and end date of each course for each student, will be recorded. We will then categorize these records by a group of days and see how many students could complete the course in the given category

Consider this table:

Now in order to categorize these records by days, use the following query:

Here’s the same query for you to try out:

DECLARE @TT Table (
CourseID int, StudentID int,
StartDt datetime, EndDt datetime
)
Insert Into @TT
SELECT 1, 1, '03/01/2011', '03/10/2011' UNION ALL
SELECT 1, 2, '03/01/2011', '03/07/2011' UNION ALL
SELECT 1, 3, '03/01/2011', '03/04/2011' UNION ALL
SELECT 2, 2, '03/01/2011', '03/11/2011' UNION ALL
SELECT 2, 4, '03/01/2011', '03/06/2011' UNION ALL
SELECT 2, 5, '03/01/2011', '03/08/2011' UNION ALL
SELECT 3, 3, '03/01/2011', '03/04/2011' UNION ALL
SELECT 3, 5, '03/01/2011', '03/05/2011' UNION ALL
SELECT 3, 6, '03/01/2011', '03/07/2011'

SELECT
CourseID,
SUM(CASE WHEN DATEDIFF(day, StartDt, EndDt)
BETWEEN 1 AND 4 THEN 1 ELSE 0 END) AS [4 or Less Days],
SUM(CASE WHEN DATEDIFF(day, StartDt, EndDt)
BETWEEN 5 AND 8 THEN 1 ELSE 0 END) AS [5 to 8 Days],
SUM(CASE WHEN DATEDIFF(day, StartDt, EndDt) > 8
THEN 1 ELSE 0 END) AS [9 or More Days]
FROM
@TT
GROUP BY CourseID

All we are doing here is using the DATEDIFF function to calculate the number of days between StartDt and EndDt and use a CASE condition to categorize records. The following output shows how many students were able to complete the course in a given range of days.

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook