# Find maximum number of concurrent logged in distinct users

I am looking out for a query providing month and year and show 4 (or 5) weeks of the selected month, and for each week it should show the maximum number of concurrently logged in users during this week. Max users should be calculated based on the SQL server table usersession. Concurrently means logged in at the same time, i.e. they have overlapping logindatetime/logout datetime. .

So if there are concurrent user if should give number of distinct users, If there are users for the date but not concurrent it should return 1 else 0.

I know it's quite hard.

• You specifically state that you want the number of distinct users, which leads me to believe that your system supports multiple concurrent sessions for the same user. Is that true or not? – mathewb Aug 27 '18 at 17:16
• First people around don't like tables in images, you can take your time to put that sample data in a format can be copyed as text. Second this kind of problem is not a really a relational one so it's best handled (in part) by a procedural language and maybe you can consider put that business rule in a Business Logic layer. Also your example is wrong because there are not sessions at day 29. – jean Aug 27 '18 at 17:26
• @jean In the question he explains that he wants the maximum number of concurrent sessions for a week period. So in the example, the first day of the week is 04/29, and that covers (presumably) 04/29 - 05/05. – mathewb Aug 27 '18 at 18:00
• @mathewb In that case it's still wrong because we got 3 guys in day 30 from 20:00 to 20:01 hour – jean Aug 27 '18 at 18:04
• What is also not clear is the definition of "week". Does a week start on Monday, Sunday or some other day? At 08:00 or at 00:00 or some other time? Does a week cross between months or not? – ypercubeᵀᴹ Aug 28 '18 at 12:50

There are standard solutions available to solve this problem by Itzik Ben-Gan and others.

There is a question over the precise meaning of "maximum number of concurrent logged in distinct users". If this means that each user might have multiple concurrent sessions, and you only want to count one per user, then we need a first step to flatten the session data into packed intervals.

## Packing Intervals

The following illustration is reproduced from Packing Intervals by Itzik Ben-Gan:

If this step is required, you can find three efficient solutions presented in that article. The one based on windowing functions, applied to the sample data is given below:

``````WITH
C1 AS
(
SELECT
event_type = +1,
sub = 1
FROM dbo.UserSession AS US
UNION ALL
SELECT
ts = US.LogoutDateTime,
event_type = -1,
sub = 0
FROM dbo.UserSession AS US
),
C2 AS
(
SELECT
C1.*,
cnt =
SUM(C1.event_type) OVER(
ORDER BY ts, C1.event_type DESC
ROWS UNBOUNDED PRECEDING) - sub
FROM C1
),
C3 AS
(
SELECT
C2.ts,
grpnum =
FLOOR
(
(
ROW_NUMBER() OVER (
ORDER BY C2.ts) - 1
) / 2 + 1
)
FROM C2
WHERE
C2.cnt = 0
)
SELECT
LogoutDateTime = MAX(ts)
FROM C3
GROUP BY
C3.grpnum
ORDER BY
``````

Now, the sample data contains no instances of overlapping sessions for the same user, so the above packing is a no-op. In general, though, it would pack the intervals as shown in the illustration.

## Number of concurrent sessions

The following standard solution was reported in Calculating Concurrent Sessions, Part 3 by Itzik as being due to Ben Flanaghan, Arnold Fribble, and R. Barry Young. The input to this stage would be the output of the Packing Intervals stage (if required).

A solution using the sample data follows:

``````WITH
UserSessions AS
(
SELECT *
FROM dbo.UserSession AS US
WHERE
AND US.LogoutDateTime < CONVERT(datetime, '20180501', 112)
),
C1 AS
(
SELECT
event_type = +1,
start_ordinal = ROW_NUMBER() OVER (
FROM UserSessions AS US
UNION ALL
SELECT
ts = US.LogoutDateTime,
event_type = -1,
start_ordinal = CONVERT(bigint, NULL)
FROM UserSessions AS US
),
C2 AS
(
SELECT
*,
start_or_end_ordinal =
ROW_NUMBER() OVER (
ORDER BY C1.ts, C1.event_type)
FROM C1
)
SELECT
mx = MAX(2 * start_ordinal - start_or_end_ordinal)
FROM C2
WHERE
C2.event_type = 1;
``````

Note the first common table expression there (UserSessions) defines the data that qualifies for the period being processed. Again, there is some question over exactly how you compute the start and end dates, but the above should provide a solid basis for you to customize.

Demo: db<>fiddle

## Complete Solution

The interval packing and concurrent sessions code can easily be wrapped in an inline function, then called once per week in the desired month period using `APPLY`. For example, the concurrent sessions function would like this:

``````CREATE FUNCTION dbo.MaxConcurrentUserSessions
(
@WeekStart datetime
)
RETURNS table
WITH SCHEMABINDING AS
RETURN
WITH
WeekSessions AS
(
SELECT
US.LogoutDateTime
FROM dbo.UserSession AS US
WHERE
AND US.LogoutDateTime < DATEADD(DAY, 7, @WeekStart)
),
C1 AS
(
SELECT
event_type = +1,
start_ordinal = ROW_NUMBER() OVER (
FROM WeekSessions AS WS
UNION ALL
SELECT
ts = WS.LogoutDateTime,
event_type = -1,
start_ordinal = CONVERT(bigint, NULL)
FROM WeekSessions AS WS
),
C2 AS
(
SELECT
C1.ts,
C1.event_type,
C1.start_ordinal,
start_or_end_ordinal =
ROW_NUMBER() OVER (
ORDER BY C1.ts, C1.event_type)
FROM C1
)
SELECT
mx = MAX(2 * start_ordinal - start_or_end_ordinal)
FROM C2
WHERE
C2.event_type = 1;
``````

Omitting the packing stage for brevity (and because it might not actually be required), the results (e.g. for April 2018) would be obtained by:

``````-- The month to report on
DECLARE @MonthStart datetime = CONVERT(datetime, '20180401');

-- Holds the start of each week in the given month
DECLARE @Weeks AS table (WeekStart datetime PRIMARY KEY);

-- Find the week start dates
INSERT @Weeks (WeekStart)
SELECT CA.WeekStart
FROM master.dbo.spt_values AS SV
CROSS APPLY
(
VALUES (DATEADD(DAY, 7 * SV.number, @MonthStart))
) AS CA (WeekStart)
WHERE SV.[type] = 'P'
AND SV.number BETWEEN 0 AND 5
AND CA.WeekStart < DATEADD(MONTH, 1, @MonthStart);

-- Find the concurrent sessions for all weeks
SELECT
W.WeekStart,
mx = ISNULL(MCUS.mx, 0)
FROM @Weeks AS W
CROSS APPLY dbo.MaxConcurrentUserSessions(W.WeekStart) AS MCUS
ORDER BY W.WeekStart;
``````

This gives:

```╔═════════════════════════╦════╗
║        WeekStart        ║ mx ║
╠═════════════════════════╬════╣
║ 2018-04-01 00:00:00.000 ║  1 ║
║ 2018-04-08 00:00:00.000 ║  0 ║
║ 2018-04-15 00:00:00.000 ║  0 ║
║ 2018-04-22 00:00:00.000 ║  0 ║
║ 2018-04-29 00:00:00.000 ║  3 ║
╚═════════════════════════╩════╝
```

Demo: db<>fiddle

The question can be better formatted by I managed to create the below script at the end it yelds the expected results:

``````FirstDayWeek MaxUsers
2018-04-01      1
2018-04-29      3
``````

First let's create an examnple:

``````create table dbo.[UserSession]
(
,[LogoutDateTime] datetime null
)
GO
/* Note a null logout datetime means that session can be counted as NOW! */

insert into dbo.[UserSession]
values
('Jsmith' ,'2018-04-01T12:01:00.000','2018-04-01T12:23:00.000')
,('Jsmith' ,'2018-04-01T14:34:00.000','2018-04-01T18:12:01.000')
,('MHoles' ,'2018-04-30T16:20:01.000','2018-04-30T20:01:15.000')
,('MHoles' ,'2018-04-01T10:00:01.000','2018-04-01T11:10:00.000')
,('Jsmith' ,'2018-04-30T20:00:01.000','2018-04-30T20:22:20.000')
GO
``````

Ok now let's find overlapping sessions, that's is pretty easy.

``````/* Find overlapping sessions */
declare
,@LogoutDateTime datetime = '2018-04-01T12:23:00.000'

select * from dbo.[UserSession] us
and
(
(us.LogoutDateTime >= @LoginDateTime and us.LogoutDateTime <= @LogoutDateTime) or -- ends in the time frame
(us.LoginDateTime < @LoginDateTime and us.LogoutDateTime > @LogoutDateTime) -- contains the time frame
)

/* Combine with sessions to create a count of parallel sessions */
select *,
(
select count(*)
from dbo.[UserSession] us
and
(
(us.LogoutDateTime >= ux.LoginDateTime and us.LogoutDateTime <= ux.LogoutDateTime) or -- ends in the time frame
(us.LoginDateTime < ux.LoginDateTime and us.LogoutDateTime > ux.LogoutDateTime) -- contains the time frame
)
) as [ParallelSessions]
from dbo.[UserSession] ux
GO
``````

Now let's to a loop to find out the weeks, they first and last days. Note it's not where relational databases shines but it's good enough

`````` /* lets get the weeks of a month */

declare @Year int = 2018, @Month int = 8
,@FirstDayMonth date, @LastDayMonth date
,@FirstDayWeek date, @LastDayWeek date
,@Day date
,@#Week smallint = 0

set @FirstDayMonth = DATEFROMPARTS(@Year, @Month, 1)
set @LastDayMonth = EOMONTH(@FirstDayMonth)

set @FirstDayWeek = @FirstDayMonth
set @Day = @FirstDayMonth

--select @FirstDayMonth, @LastDayMonth, DATEPART(dw, @FirstDayMonth)

begin
if DATEPART(dw, @Day) = 1 OR @Day = @FirstDayMonth
begin
set @FirstDayWeek = @Day
end

if DATEPART(dw, @Day) = 7 OR @Day = @LastDayMonth
begin
set @LastDayWeek = @Day
set @#Week = @#Week + 1

select @#Week as [#Week], @FirstDayWeek as [FirstDayWeek], @LastDayWeek as [LastDayWeek]
end

set @Day = DATEADD(DAY, 1, @Day)
end

/* Now you needs to define how a day can be counted,
a sesion is on that day because it started or ended or somehow overlaps with a day?
Let assume a session last multiple days and can be counted

But I will let that considerations for OP
*/
GO
``````

Ok, let's put all ingredients together and cook it.

``````/* Let's combine all above all put all in a table variable */

declare @ParallelSessionsByWeek Table
(
[#week] smallint not null
,[FirstDayWeek] date not null
,[LastDayWeek] date null
,[LogoutDateTime] datetime not null -- Here I hope is not null for sake of simplicity
,[ParallelSessions] int not null
)

declare @Year int = 2018, @Month int = 4
,@FirstDayMonth date, @LastDayMonth date
,@FirstDayWeek date, @LastDayWeek date
,@Day date
,@#Week smallint = 0

set @FirstDayMonth = DATEFROMPARTS(@Year, @Month, 1)
set @LastDayMonth = EOMONTH(@FirstDayMonth)

set @FirstDayWeek = @FirstDayMonth
set @Day = @FirstDayMonth

begin
if DATEPART(dw, @Day) = 1 OR @Day = @FirstDayMonth
begin
set @FirstDayWeek = @Day
end

if DATEPART(dw, @Day) = 7 OR @Day = @LastDayMonth
begin
set @LastDayWeek = @Day
set @#Week = @#Week + 1

-- select @#Week as [#Week], @FirstDayWeek as [FirstDayWeek], @LastDayWeek as [LastDayWeek]
------------------------------------------------------------------------------------------
insert into @ParallelSessionsByWeek
select @#Week, @FirstDayWeek, @LastDayWeek,
(
select count(*) + 1 -- 1 for zero parallel
from dbo.[UserSession] us
where
and
(
(us.LogoutDateTime >= ux.LoginDateTime and us.LogoutDateTime <= ux.LogoutDateTime) or -- ends in the time frame
(us.LoginDateTime < ux.LoginDateTime and us.LogoutDateTime > ux.LogoutDateTime) -- contains the time frame
)
) as [ParallelSessions]
from dbo.[UserSession] ux