0

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.