SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE dbo.[UserSession]
(
     [UserName] varchar(50) NOT NULL,
     [LoginDateTime] datetime NOT NULL,
     [LogoutDateTime] datetime NULL
);

INSERT dbo.[UserSession]
    (UserName, LoginDateTime, LogoutDateTime)
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');
7 rows affected

-- INTERVAL PACKING
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;


UserName
LoginDateTime
LogoutDateTime


MHoles
01/04/2018 10:00:01
01/04/2018 11:10:00

Jsmith
01/04/2018 12:01:00
01/04/2018 12:23:00

Jsmith
01/04/2018 14:34:00
01/04/2018 18:12:01

Adownon
02/04/2018 11:11:00
02/04/2018 15:31:00

Adownon
30/04/2018 15:43:01
30/04/2018 21:32:50

MHoles
30/04/2018 16:20:01
30/04/2018 20:01:15

Jsmith
30/04/2018 20:00:01
30/04/2018 20:22:20


-- MAXIMUM CONCURRENT SESSIONS
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;


mx


3

UserName	LoginDateTime	LogoutDateTime
MHoles	01/04/2018 10:00:01	01/04/2018 11:10:00
Jsmith	01/04/2018 12:01:00	01/04/2018 12:23:00
Jsmith	01/04/2018 14:34:00	01/04/2018 18:12:01
Adownon	02/04/2018 11:11:00	02/04/2018 15:31:00
Adownon	30/04/2018 15:43:01	30/04/2018 21:32:50
MHoles	30/04/2018 16:20:01	30/04/2018 20:01:15
Jsmith	30/04/2018 20:00:01	30/04/2018 20:22:20