Login, Logout tracking using SQL-CodePudding

I am trying to summarize the login, logout information of a user. If a user has one of the event types 1 or 3 both are considered as login event. If a user has 6,7 or 13 then it is considered as logout event. Each time the user has this event type, I want to consider the first event that happens and calculate how long they were logged in.

Here is the sample Data:

    DECLARE @T AS TABLE
    (
                ID          INT,
                access_time datetime,
                EventId varchar(10),
                EventType varchar(25)
    )
        
insert into @T VALUES

(123,'2021-10-15 06:00:29', 1, 'Autheticated' ),
(123,'2021-10-15 06:00:39', 3, 'Loggedin' ),
(123,'2021-10-15 08:00:14', 6, 'Loggedout' ),
(123,'2021-10-15 13:00:39', 3, 'Loggedin' ),
(123,'2021-10-16 00:00:12', 6, 'Loggedout' ),
(123,'2021-10-16 00:00:39', 7, 'Timedout' ),
(123,'2021-10-16 00:15:40', 13, 'ApplicationClosed' ),
(123,'2021-10-17 04:32:16', 3, 'Loggedin' ),
(123,'2021-10-17 15:45:20', 7, 'Timedout' ),
(123,'2021-10-17 15:47:40', 13, 'ApplicationClosed' )

DECLARE @final AS TABLE
(
    ID          INT,
    Login_time datetime,
    Logout_time datetime 
)

insert into @final VALUES

(123,'2021-10-15 06:00:29', '2021-10-15 08:00:14'),
(123,'2021-10-15 13:00:39', '2021-10-16 00:00:12'),
(123,'2021-10-17 04:32:16', '2021-10-17 15:45:20')
        
select * from @final

I tried using the lead function and I am not getting the results as expected due to missing events for some days. How to get data in @final formatted without using the lead function?

CodePudding user response：

This is a type of gaps-and-islands problem.

There are a number of solutions, here is one

Calculate starting points for each set of rows using LAG
Using a running conditional count, calculate an ID for each of those groups
Then simply group up using those IDs

WITH StartPoints AS (
    SELECT *,
      IsStart = CASE WHEN EventId IN (1,3) AND LAG(EventId, 1, -1) OVER (PARTITION BY ID ORDER BY access_time) NOT IN (1,3) THEN 1 END
    FROM @T
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(IsStart) OVER (PARTITION BY ID ORDER BY access_time)
    FROM StartPoints
)
SELECT
  ID,
  Login_time = MIN(access_time),
  Logout_time = MAX(CASE WHEN EventId IN (6,7,13) THEN access_time END)
FROM Groupings g
GROUP BY
  ID,
  GroupId;

db<>fiddle