CODEWITHSUNDEEP
sql-servert-sql
attic_view
attic_view

Reputation: 48

How to output concurrent users in SQL Server

I want to produce a query that outputs the number of concurrent users between certain times of the day. I will be using a variable to allow me to select a particular day.

The table has a login_time field which is datetime, and a logout_time field which is also datetime. Basically, I want to be able to output to an hour column if the user is logged in at any point during that hour.

A case statement wouldn't be scalable, so if I wanted to reduce to fifteen minute segments rather than an hour, I would effectively need 48 case statements.

Is there a better way to handle a query such as this to output concurrent user logins? Happy for a solution to be delivered anywhere in the MSBI stack.

Upvotes: 2

Views: 327

Answers (2)

Rich Benner
Rich Benner

Reputation: 8113

If you've got set times that you're doing the query for (say the shortest is 15 minutes) then you could create a date table to link to rather than calculate it on the fly.

Say we had this sample data;

CREATE TABLE #TestData (loginID int, login_time datetime, logout_time datetime)
INSERT INTO #TestData (loginID, login_time, logout_time)
VALUES
('1','2016-06-08 09:10:00','2016-06-08 10:10:00')
,('2','2016-06-08 08:55:00','2016-06-08 10:55:00')
,('3','2016-06-08 09:29:00','2016-06-08 10:29:00')
,('4','2016-06-08 09:40:00','2016-06-08 10:40:00')
,('5','2016-06-08 09:08:00','2016-06-08 10:08:00')
,('6','2016-06-08 09:04:00','2016-06-08 10:04:00')
,('7','2016-06-08 09:12:00','2016-06-08 10:12:00')
,('8','2016-06-08 09:40:00','2016-06-08 10:40:00')
,('9','2016-06-08 09:21:00','2016-06-08 10:21:00')

You could create a date table like this (this is a very simplified version for this example);

CREATE TABLE #DateTimeTable (ReferenceDate datetime, RefHour datetime)
INSERT INTO #DateTimeTable
VALUES
('2016-06-08 08:45:00','2016-06-08 08:00:00')
,('2016-06-08 09:00:00','2016-06-08 09:00:00')
,('2016-06-08 09:15:00','2016-06-08 09:00:00')
,('2016-06-08 09:30:00','2016-06-08 09:00:00')
,('2016-06-08 09:45:00','2016-06-08 09:00:00')
,('2016-06-08 10:00:00','2016-06-08 10:00:00')
,('2016-06-08 10:15:00','2016-06-08 10:00:00')
,('2016-06-08 10:30:00','2016-06-08 10:00:00')

Then if you wanted the results for users by every 15 minutes you could use this query;

SELECT 
dtt.ReferenceDate
,COUNT(td.loginID) Users
FROM #TestData td
CROSS JOIN #DateTimeTable dtt
WHERE td.login_time <= dtt.ReferenceDate
    AND td.logout_time > dtt.ReferenceDate
GROUP BY dtt.ReferenceDate

Which gives this result;

ReferenceDate                Users
2016-06-08 09:00:00.000      1
2016-06-08 09:15:00.000      5
2016-06-08 09:30:00.000      7
2016-06-08 09:45:00.000      9
2016-06-08 10:00:00.000      9
2016-06-08 10:15:00.000      5
2016-06-08 10:30:00.000      3

If you wanted it by hour then you could use this one;

SELECT 
dtt.RefHour
,COUNT(td.loginID) Users
FROM #TestData td
CROSS JOIN (SELECT DISTINCT RefHour FROM #DateTimeTable) dtt
WHERE td.login_time <= dtt.RefHour
    AND td.logout_time > dtt.RefHour
GROUP BY dtt.RefHour

To give this result;

RefHour                   Users
2016-06-08 09:00:00.000   1
2016-06-08 10:00:00.000   9

The advantage of the table is that once it's created you can use the results in all number of queries within your database in order to increase performance.

Upvotes: 2

Cato
Cato

Reputation: 3701

here's a way of getting timeslots of a defined length during the day, you can then JOIN or CROSS APPLY or otherwise use the TIMESLOTS CTE in queries to summarize data within those slots - timeslot length would be 60 in your case

DECLARE @SLOTMINS AS INTEGER;

SET @SLOTMINS = 15;

;with TIMESLOTS AS
    (SELECT CAST('00:00' as datetime) as start, 0 as mins
    UNION ALL 
    SELECT dateadd(n, @SLOTMINS, TIMESLOTS.start), mins + @SLOTMINS from TIMESLOTS WHERE  mins + @SLOTMINS < 1440
    )
    select * from TIMESLOTS

Upvotes: 1

Related Questions