CODEWITHSUNDEEP
sqldatetimesql-server-2008-r2group-by
DarkLight
DarkLight

Reputation: 91

Grouping records between start and end time

I have a database of tests, each with a StartTime and (for those finished) EndTime value. I want to create a query that would display the number of tests that were running each hour.

i.e. Tests that had a startTime but not an EndTime at any given hour.

I worked out the grouping thing - with thanks to this answer from @p.cambell to the question SQL Server Group by Count of DateTime Per Hour?

Upvotes: 0

Views: 1408

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271051

You can do this with conditional aggregation:

SELECT CAST(StartDate as date) AS ForDate,
       DATEPART(hour, StartDate) AS OnHour,
       COUNT(*) AS TotalStarts,
       SUM(CASE WHEN CAST(StartDate as date) <> CAST(EndDate as date) or
                     DATEPART(hour, StartDate) <> DATEPART(hour, EndDate)
                THEN 1
                ELSE 0
           END) as StartedButNotEndedInHour
FROM #Events
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

Upvotes: 1

Related Questions