SQL max concurrent sessions per hour of day

Question

I work in a healthcare call-centre environment. I have access to our core system's database and I want to use the data in it to determine the number of staff logged on each hour of the data in a given period that may expand over 24 hours (e.g. 24/12/2014 - 26/12/2014).

There is a userlog table that records when users log on and log off.

userlog table example

UserRef                                 Date                    LogType SessionID   
--------------------------------------- ----------------------- ------  -----------------------
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 14:11:25.763 LOGON   8D451569-0260-46BB-9B9E-F49B3E778161    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 18:11:32.547 LOGOFF  8D451569-0260-46BB-9B9E-F49B3E778161    
60738820-5F72-4E20-A070-57E07C83B6DE    2002-10-17 14:53:31.153 LOGON   C773894C-8B2D-4054-A550-3F04B4C5669F    
60738820-5F72-4E20-A070-57E07C83B6DE    2002-10-17 22:55:25.607 LOGOFF  C773894C-8B2D-4054-A550-3F04B4C5669F
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:26:40.123 LOGON   1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 15:51:28.590 LOGON   7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:58:05.217 LOGOFF  1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7    
96AD647C-D061-43F5-9F8D-FA6C74817E07    2002-10-17 15:58:31.013 LOGOFF  7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 15:58:32.733 LOGON   03F56AB8-FED5-4CC7-8445-26BF55F58E60    
90A55FDD-967E-4D99-96DF-96840CDB2CDF    2002-10-17 16:13:02.827 LOGOFF  03F56AB8-FED5-4CC7-8445-26BF55F58E60

Desired results (this is not intended to reflect the above sample data):

Date         Hour    Number of users logged in
----         ----    -------------------------
01/12/2014    0                   0
01/12/2014    1                   0
01/12/2014    2                   0 
01/12/2014    3                   0
01/12/2014    4                   0
01/12/2014    5                   1
01/12/2014    6                   1
01/12/2014    7                   1
01/12/2014    8                   3
01/12/2014    9                   7
01/12/2014    10                  7
...                 
01/12/2014    23                  0

To be clear: I'm trying to get the hour to still display with a 0 count when there were no users logged in.

I guess what I'm looking for is the maximum concurrent sessions for each hour of the day, but I'm not overly technical or skilled in SQL (getting better bit by bit, though!) so I hope that terminology doesn't confuse things!

I've googled for this and found a few similar scenarios, but for Oracle and MySQL, or where the log table records the logon data differently. I'm sure I'll get to a point where I can successfully 'translate' other database query code to MS SQL, but I'm not there yet!

I am using: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86).

Thanks in advance for your help.

will you pass the date/date range in query to specify that I want results of this particular date/date range? also, share your tries. — Deep, Dec 30 '14 at 11:08
@DeepakPawar Yes I will. I didn't get anywhere with tries, except to get it into a table with column headers of session id, logon date, logoff date. I didn't know where to go from there. Sorry :( — jjswin, Dec 30 '14 at 11:15

score 1 · Accepted Answer · 2014-12-31T11:46:17.497

UPDATED (v # 4):

I've added date filter at the end of the query - WHERE A.[Date] Between @X and @Y - it's not most efficient way in this case but simplest I think and less error prone for a start :

SELECT
  A.[Date],
  A.[Hour],
  SUM(CASE WHEN (B.[SessionID] IS NULL) THEN 0 ELSE 1 END) AS [Number_of_Sessions_Per_Hour]
FROM
(
    SELECT DISTINCT
        CONVERT(DATETIME,
            LTRIM(RTRIM(CONVERT(NVARCHAR(10), YEAR(userlog.[Date]))))
            + '-' + RIGHT('0' + LTRIM(RTRIM(CONVERT(NVARCHAR(10), MONTH(userlog.[Date])))), 2)
            + '-' + RIGHT('0' + LTRIM(RTRIM(CONVERT(NVARCHAR(10), DAY(userlog.[Date])))), 2)
            , 120) AS [Date],
        hours_table.[Hour]
    FROM
        userlog,
        (
            SELECT 1 AS [Hour] UNION ALL SELECT 3 AS [Hour] UNION ALL SELECT 4 AS [Hour] UNION ALL SELECT 5 AS [Hour] UNION ALL SELECT 6 AS [Hour]
             UNION ALL SELECT 7 AS [Hour] UNION ALL SELECT 7 AS [Hour] UNION ALL SELECT 8 AS [Hour] UNION ALL SELECT 9 AS [Hour] UNION ALL SELECT 10 AS [Hour]
             UNION ALL SELECT 11 AS [Hour] UNION ALL SELECT 12 AS [Hour] UNION ALL SELECT 13 AS [Hour] UNION ALL SELECT 14 AS [Hour] UNION ALL SELECT 15 AS [Hour]
             UNION ALL SELECT 16 AS [Hour] UNION ALL SELECT 17 AS [Hour] UNION ALL SELECT 18 AS [Hour] UNION ALL SELECT 19 AS [Hour] UNION ALL SELECT 20 AS [Hour]
             UNION ALL SELECT 21 AS [Hour] UNION ALL SELECT 22 AS [Hour] UNION ALL SELECT 23 AS [Hour] UNION ALL SELECT 24 AS [Hour] 
        ) as hours_table
) AS A
LEFT OUTER JOIN
(
    SELECT 
        userlog.SessionID,
        MAX(CASE WHEN userlog.LogType = 'LOGON' 
                 THEN CONVERT(DATETIME,
                        LTRIM(RTRIM(CONVERT(NVARCHAR(10), YEAR(userlog.[Date]))))
                        + '-' + RIGHT('0' + LTRIM(RTRIM(CONVERT(NVARCHAR(10), MONTH(userlog.[Date])))), 2)
                        + '-' + RIGHT('0' + LTRIM(RTRIM(CONVERT(NVARCHAR(10), DAY(userlog.[Date])))), 2)
                        , 120)
                 ELSE CONVERT(DATETIME, '1900-01-01', 120)
            END) AS [Date_Session_START],
        MAX(CASE WHEN userlog.LogType = 'LOGOFF' 
                 THEN CONVERT(DATETIME,
                        LTRIM(RTRIM(CONVERT(NVARCHAR(10), YEAR(userlog.[Date]))))
                        + '-' + RIGHT('0' + LTRIM(RTRIM(CONVERT(NVARCHAR(10), MONTH(userlog.[Date])))), 2)
                        + '-' + RIGHT('0' + LTRIM(RTRIM(CONVERT(NVARCHAR(10), DAY(userlog.[Date])))), 2)
                        , 120)
                 ELSE CONVERT(DATETIME, '1900-01-01', 120)
            END) AS [Date_Session_END],
        MAX(CASE WHEN userlog.LogType = 'LOGON' THEN DATEPART(HOUR, userlog.[Date]) ELSE 0 END) AS [Hour_Session_START],
        MAX(CASE WHEN userlog.LogType = 'LOGOFF' THEN DATEPART(HOUR, userlog.[Date]) ELSE 0 END) AS [Hour_Session_END],
    FROM
        userlog
    GROUP BY
        userlog.SessionID
) AS B
    ON (A.[Date] >= B.[Date_Session_START] AND A.[Date] <= B.[Date_Session_END])
        AND (A.[Hour] >= B.[Hour_Session_START] AND A.[Hour] <= B.[Hour_Session_END])
WHERE
    A.[Date] Between @X and @Y
GROUP BY
  A.[Date],
  A.[Hour]

Hi, the results look a bit low - is your query counting the number of logon events per hour or the number of active sessions per hour (i.e. you'd still get a count if the user hadn't yet logged off but had logged in the previous hour). EDIT - sorry I meant to thank you for answer as well :) — jjswin, Dec 30 '14 at 12:02
No problem :) Please tell which rows should be teaken to result then? All or `LogType = 'LOGON'` or `LogType = 'LOGOFF'` - and should it count sessions or users per hour? Or maybe sessions per user per hour? — , Dec 30 '14 at 12:05
It should count against each hour of the day any sessions where the hour of the day is equal or greater than the hour of the LOGON datetime but less than or equal to the hour of the LOGOFF datetime. — jjswin, Dec 30 '14 at 12:23
What about the situation: user `User1` logged in and 2 times between hours 13 and 14, then there are 4 records `User1, Session1, LOGON`, `User1, Session1, LOGOFF`, `User1, Session2, LOGON`, `User1, Session2, LOGOFF`. Should it count: `Hour 13: 1` or `Hour 13: 2` ? What should it take into account: distinct users in an hour or distinct sessions? — , Dec 30 '14 at 12:37
In reality this wouldn't happen, that's my fault I'm afraid. They are limited to one session per user. — jjswin, Dec 30 '14 at 12:54
No problem, thx for reply. Please find the updated answer above - I tried to adjust it to your comment. I would be grateful for the response, whether the solution works for your case. — , Dec 30 '14 at 12:56
Hi thanks, just getting an error message here - Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier. — jjswin, Dec 30 '14 at 13:03
@jjswin: **UPDATE** Edited 5 secs ago - the problem with the UNIQUEIDENTIFIER also should now be fixed. — , Dec 30 '14 at 13:15
Hi, I had to deal with something urgent yesterday, apologies for not getting back to you sooner. Just tried it now - I need to know where to filter on the date? i.e. `WHERE userlog.[Date] between x and y`? I tried putting it in myself but got results where there were multiple entries for each hour. — jjswin, Dec 31 '14 at 11:37
Thank you - the results look like they might be right!!!! I will go away and compare with the raw data and work it out manually. I probably won't be able to do this til Friday, though what with NYE celebrations! :) Thanks so much for your commitment. — jjswin, Dec 31 '14 at 13:19
I'm really glad :) Wish you all the best in New Year 2015 then :) And thanks for good cooperation in problem solving. — , Jan 08 '15 at 16:27

u07ch · Answer 2 · 2014-12-30T14:52:19.777

OK, taken a slightly different approach. I build a table of Logon / Log Off - which could be in a cte but Im not an oracle expert so not sure if its supported so gone for simpler SQL and a temp table. I have all built a table of static integers (also known as tally table) for 0-30 to handle a test date range in my query based on your answer above - if you have one already you could just use that ranged down and save some effort. Running the query returns a table like this (using your sample data also below)

StartDate   WorkingHour LoggedInUsers
2002-10-17 00:00:00.000 10  0
2002-10-17 00:00:00.000 11  0
2002-10-17 00:00:00.000 12  0
2002-10-17 00:00:00.000 13  0
2002-10-17 00:00:00.000 14  2
2002-10-17 00:00:00.000 15  5
2002-10-17 00:00:00.000 16  3
2002-10-17 00:00:00.000 17  2
2002-10-17 00:00:00.000 18  2
2002-10-17 00:00:00.000 19  1
2002-10-17 00:00:00.000 20  1
2002-10-17 00:00:00.000 21  1
2002-10-17 00:00:00.000 22  1
2002-10-17 00:00:00.000 23  0

Code as Run in sql

DECLARE @StartDate DATETIME ,
@NoDays INT ;


select
@StartDate = '2002-10-01',
@NoDays = 20;



DECLARE @Sessions TABLE (
UserRef UNIQUEIDENTIFIER,
DATE DATETIME,
LogType VARCHAR(100), 
SessionID UNIQUEIDENTIFIER
);
INSERT INTO @Sessions
SELECT '96AD647C-D061-43F5-9F8D-FA6C74817E07', '2002-10-17 14:11:25.763', 'LOGON', '8D451569-0260-46BB-9B9E-F49B3E778161'
UNION SELECT '96AD647C-D061-43F5-9F8D-FA6C74817E07', '2002-10-17 18:11:32.547', 'LOGOFF', '8D451569-0260-46BB-9B9E-F49B3E778161'
UNION SELECT '60738820-5F72-4E20-A070-57E07C83B6DE', '2002-10-17 14:53:31.153', 'LOGON', 'C773894C-8B2D-4054-A550-3F04B4C5669F'
UNION SELECT '60738820-5F72-4E20-A070-57E07C83B6DE', '2002-10-17 22:55:25.607', 'LOGOFF', 'C773894C-8B2D-4054-A550-3F04B4C5669F'
UNION SELECT '90A55FDD-967E-4D99-96DF-96840CDB2CDF', '2002-10-17 15:26:40.123', 'LOGON', '1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7'
UNION SELECT '96AD647C-D061-43F5-9F8D-FA6C74817E07', '2002-10-17 15:51:28.590', 'LOGON', '7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73'
UNION SELECT '90A55FDD-967E-4D99-96DF-96840CDB2CDF', '2002-10-17 15:58:05.217', 'LOGOFF', '1CE5F5A5-4E20-4D4A-BB67-EB0CB33976D7'
UNION SELECT '96AD647C-D061-43F5-9F8D-FA6C74817E07', '2002-10-17 15:58:31.013', 'LOGOFF', '7EFDEE1C-15CF-4DE1-B59F-7AFC49B4BE73'
UNION SELECT '90A55FDD-967E-4D99-96DF-96840CDB2CDF', '2002-10-17 15:58:32.733', 'LOGON', '03F56AB8-FED5-4CC7-8445-26BF55F58E60'
UNION SELECT '90A55FDD-967E-4D99-96DF-96840CDB2CDF', '2002-10-17 16:13:02.827', 'LOGOFF', '03F56AB8-FED5-4CC7-8445-26BF55F58E60';  


DECLARE @staticintegers TABLE (myInteger INT);
INSERT INTO @staticintegers 
SELECT 0 UNION SELECT 1  UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12 UNION SELECT 13
UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18
UNION SELECT 19 UNION SELECT 20 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23
UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32 UNION SELECT 33;

DECLARE @sessionsOutput TABLE (SessionID UNIQUEIDENTIFIER, StartTime DATETIME, EndTime DATETIME);
INSERT INTO @sessionsOutput
(SessionID, StartTime)
SELECT 
    SessionID,
    [date]
FROM 
    @Sessions
    WHERE logtype = 'Logon';

UPDATE @sessionsOutput
SET EndTime = [date]
FROM @sessionsOutput aa
INNER JOIN @Sessions bb
ON aa.SessionID = bb.SessionID
WHERE bb.LogType = 'Logoff';



SELECT
    DATEADD(dd, DateIntegers.myInteger, @StartDate) AS StartDate
    ,hoursintegers.myINteger AS WorkingHour
    ,COUNT(aa.SessionID) AS LoggedInUsers


FROM 
    @staticintegers DateIntegers
    LEFT OUTER JOIN @StaticIntegers HoursIntegers
    ON HoursIntegers.myInteger BETWEEN 0 AND 23

LEFT OUTER JOIN @sessionsOutput aa
ON
    HoursIntegers.myInteger BETWEEN DATEPART(hh, aa.StartTime) AND DATEPART(hh, aa.endtime)
    AND CAST(aa.StartTime AS DATE) = DATEADD(dd, dateintegers.myInteger, @StartDate) 

GROUP BY 
    DATEADD(dd, DateIntegers.myInteger, @StartDate),
    HoursIntegers.myInteger
ORDER BY 
    DATEADD(dd, DateIntegers.myInteger, @StartDate),
    HoursIntegers.myInteger;

Hi! Thank you. I've not got it working yet however; I had to fiddle with the SQL as it was moaning that the variables hadn't been declared (I added the SET bit), and then about date not being a valid type. Anyway, I got it running and it's returning 0 for each hour. — jjswin, Dec 30 '14 at 12:50
Been a while since I Have been in SQL 2005; connected to one and tried it; changing the header to - declare and then select (can;t do in comment but will update query above) Doing this Allowed me to run the query as submitted in 2005 without and when scrolled down to the 17th (per your example data - the query returns from no days from the start date which i am using as 1st October 2002 in the example as its just before your sample data) I can still see the results as posted. — u07ch, Dec 30 '14 at 14:49

SQL max concurrent sessions per hour of day

2 Answers2

Linked