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:

sqlgroupbydays

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

groupbydate

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

image


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

No comments: