Group by Hour within start enddate

Question

Im hoping you guys can help me.

I have a table full of users logging in and out. Example row: username,startdatetime,enddatetime

Im trying to select hourly how many users were active. I Found the following:

select Time_Stamp_Hour=dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime)), Count(*) as amount
from dbo.Sessions
group by dateadd(hh,datepart(hh,StartDateTime), cast(CAST(StartDateTime as date) as datetime))
order by Time_Stamp_Hour desc

This indeed groups together the amount of sessions started per hour. but im looking for how many would be active. i cannot seem to figure out how i would build a query like that. So bassicly grouped per hour a count which falls between Start and End datetime

Answer 1

You can create a table of 24 rows representing the 24 hours in a day to check the number of active users in each hour. Example below.

NOTE: In my example, I only really have data for a single day. You can add a WHERE clause to the subquery to filter larger data sets to a single day to check active users for a single day.

Setup:

CREATE TABLE #Hours (Hour INT)

;WITH CTE AS (
    SELECT 1 AS Level
    UNION ALL
    SELECT 1+Level
    FROM CTE
    WHERE Level <= 23
)

INSERT INTO #Hours (Hour)
SELECT Level 
FROM CTE

CREATE TABLE #Sessions (UserID INT,
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO #Sessions (UserID, StartDate, EndDate)
VALUES
(1, '2019-05-29 08:00:00', '2019-05-29 16:00:00')
,(2, '2019-05-29 10:00:00', '2019-05-29 21:00:00')
,(3, '2019-05-29 02:00:00', '2019-05-29 08:00:00')
,(4, '2019-05-29 15:00:00', '2019-05-29 23:00:00')
,(5, '2019-05-29 09:00:00', '2019-05-29 17:00:00')
,(6, '2019-05-29 13:00:00', '2019-05-29 18:00:00')
,(7, '2019-05-29 04:00:00', '2019-05-30 00:00:00')

Query:

SELECT h.Hour,
    COUNT(UserID) AS [ActiveUsers]
FROM 
(
    SELECT UserID, StartDate, EndDate, 
        CASE 
            WHEN DATEPART(HOUR, StartDate) = 0 THEN 24 
            ELSE DATEPART(HOUR, StartDate)
        END AS StartHour,
        CASE 
            WHEN DATEPART(HOUR, EndDate) = 0 THEN 24 
            ELSE DATEPART(HOUR, EndDate)
        END AS EndHour
    FROM #Sessions
) s
FULL OUTER JOIN #Hours h ON h.Hour BETWEEN StartHour AND EndHour
GROUP BY h.Hour

Results:

Hour    ActiveUsers
-------------------
1       0
2       1
3       1
4       2
5       2
6       2
7       2
8       3
9       3
10      4
11      4
12      4
13      5
14      5
15      6
16      6
17      5
18      4
19      3
20      3
21      3
22      2
23      2
24      1