-2

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. .Table and example

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.

  • 2
    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
  • 1
    @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
  • 3
    @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
  • 1
    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
1

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:

(c) 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
            US.UserName,
            ts = US.LoginDateTime,
            event_type = +1,
            sub = 1
        FROM dbo.UserSession AS US
        UNION ALL
        SELECT
            US.UserName,
            ts = US.LogoutDateTime,
            event_type = -1,
            sub = 0
        FROM dbo.UserSession AS US
    ),
    C2 AS
    (
        SELECT 
            C1.*,
            cnt =
                SUM(C1.event_type) OVER(
                    PARTITION BY C1.UserName 
                    ORDER BY ts, C1.event_type DESC
                    ROWS UNBOUNDED PRECEDING) - sub
        FROM C1
    ),
    C3 AS
    (
        SELECT
            C2.UserName,
            C2.ts,
            grpnum =
                FLOOR
                (
                    (
                        ROW_NUMBER() OVER (
                            PARTITION BY C2.UserName 
                            ORDER BY C2.ts) - 1
                    ) / 2 + 1
                )
        FROM C2
        WHERE
            C2.cnt = 0
    )
SELECT
    C3.UserName,
    LoginDateTime = MIN(ts),
    LogoutDateTime = MAX(ts)
FROM C3
GROUP BY
    C3.UserName,
    C3.grpnum
ORDER BY
    LoginDateTime;

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
            US.LoginDateTime >= CONVERT(datetime, '20180429', 112)
            AND US.LogoutDateTime < CONVERT(datetime, '20180501', 112)
    ),
    C1 AS
    (
        SELECT
            ts = US.LoginDateTime,
            event_type = +1,
            start_ordinal = ROW_NUMBER() OVER (
                ORDER BY US.LoginDateTime)
        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.UserName,
                US.LoginDateTime,
                US.LogoutDateTime 
            FROM dbo.UserSession AS US
            WHERE
                US.LoginDateTime >= @WeekStart
                AND US.LogoutDateTime < DATEADD(DAY, 7, @WeekStart)
        ),
        C1 AS
        (
            SELECT
                ts = WS.LoginDateTime,
                event_type = +1,
                start_ordinal = ROW_NUMBER() OVER (
                    ORDER BY WS.LoginDateTime)
            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

1

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]
(
 [UserName] varchar(50) not null
,[LoginDateTime] datetime not null
,[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')
,('Adownon','2018-04-02T11:11:00.000','2018-04-02T15:31:00.000')
,('Jsmith' ,'2018-04-01T14:34:00.000','2018-04-01T18:12:01.000')
,('Adownon','2018-04-30T15:43:01.000','2018-04-30T21:32:50.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
 @UserName varchar(50) = 'Jsmith'
,@LoginDateTime datetime = '2018-04-01T12:01:00.000'
,@LogoutDateTime datetime = '2018-04-01T12:23:00.000'

select * from dbo.[UserSession] us 
where us.UserName != @UserName
and
(
    (us.LoginDateTime >= @LoginDateTime and us.LoginDateTime <= @LogoutDateTime) or -- starts in the time frame
    (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 
    where us.UserName != ux.UserName
    and
    (
        (us.LoginDateTime >= ux.LoginDateTime and us.LoginDateTime <= ux.LogoutDateTime) or -- starts in the time frame
        (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)

while @Day <  DATEADD(DAY, 1,@LastDayMonth)
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
   ,[UserName] varchar(50) not null
   ,[LoginDateTime] datetime not 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


while @Day <  DATEADD(DAY, 1,@LastDayMonth)
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
        ([#week],[FirstDayWeek],[LastDayWeek],[UserName],[LoginDateTime],[LogoutDateTime],[ParallelSessions])
        select @#Week, @FirstDayWeek, @LastDayWeek,
        [UserName],[LoginDateTime],[LogoutDateTime],
        (
            select count(*) + 1 -- 1 for zero parallel
            from dbo.[UserSession] us 
            where
            us.LoginDateTime >= @FirstDayWeek and us.LogoutDateTime < DATEADD(DAY, 1, @LastDayWeek)
            and us.UserName != ux.UserName
            and
            (
                (us.LoginDateTime >= ux.LoginDateTime and us.LoginDateTime <= ux.LogoutDateTime) or -- starts in the time frame
                (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
         where ux.LoginDateTime >= @FirstDayWeek and ux.LogoutDateTime < DATEADD(DAY, 1, @LastDayWeek)
------------------------------------------------------------------------------------------
   end

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

select psw.FirstDayWeek, max(ParallelSessions) as [MaxUsers] from @ParallelSessionsByWeek psw
group by psw.FirstDayWeek

And that gives us the expected results.

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.