Show two rows related to each other as one base on date from same table-CodePudding

I am trying to write query for below scenario. When user logs in we log LoginSuccess event and when log out we calculate total duration for which he was logged in and log it as event Duration.

I want to display both logs for a user when he login and what was the duration event 'Arg' for that respective login.

Below is the sample data and output

CREATE TABLE [logs_table] 
             ([AccountId] [uniqueidentifier] NULL,
              [UtcActionDate] [datetime] NULL,
              [ActionType] [nvarchar](255) NULL,
              [ActionSubType] [nvarchar](255) NULL,
              [Arg] [nvarchar](255) NULL);

INSERT INTO logs_table
(AccountId,UtcActionDate,ActionType,ActionSubType,Arg)
VALUES ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-10 17:32:19.000', 'Login','LoginSuccess',null),
       ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-10 21:54:56.000', 'Exchange','Duration','01:03:41),
       ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-11 23:00:38.000', 'Login','LoginSuccess',null),
       ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662',2022-03-14 17:39:47.000', 'Exchange','Duration','00:00:17'),
       ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-14 17:40:30.000', 'Login','LoginSuccess',null),
       ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-14 21:10:08.000', 'Exchange','Duration','00:00:25')

Sample output will look like

AccoundId                            LoginStartDate LoginStartTime Arg
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 2022-03-10     17:32:19.000  01:03:41 
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 2022-03-11     23:00:38.000  00:00:17 
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 2022-03-14     17:40:30.000  00:00:25

User can log in multiple time in a day and there can be multiple loginsuccess event before the duration event logs and I need to do this inside a view so no cursors or temp tables.

CodePudding user response：

You can return the most recent successful login before or equal to the duration event log action.

CREATE TABLE logs_table (   [AccountId] [uniqueidentifier] NULL,
                            [UtcActionDate] [datetime] NULL,
                            [ActionType] [nvarchar](255) NULL,
                            [ActionSubType] [nvarchar](255) NULL,
                            [Arg] [nvarchar](255) NULL
                        );

INSERT INTO logs_table 
(AccountId,UtcActionDate,ActionType,ActionSubType,Arg) 
VALUES 
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-10 17:32:19.000', 'Login','LoginSuccess',null),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-10 21:54:56.000', 'Exchange','Duration','01:03:41'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-11 23:00:38.000', 'Login','LoginSuccess',null),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-14 17:39:47.000', 'Exchange','Duration','00:00:17'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-14 17:40:30.000', 'Login','LoginSuccess',null),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-03-14 21:10:08.000', 'Exchange','Duration','00:00:25');

WITH logins
AS
(
    SELECT *
    FROM logs_table
    WHERE ActionType = 'Login'
    AND ActionSubType = 'LoginSuccess'
),
durations
AS
(
    SELECT *
    FROM logs_table
    WHERE ActionType = 'Exchange'
    AND ActionSubType = 'Duration'
),
both
AS
(
SELECT   x.AccountId
        ,x.UtcActionDate
        ,y.Arg
        ,MAX(x.UtcActionDate) OVER (PARTITION BY y.AccountId, y.UtcActionDate, y.Arg) AS max_login_date
FROM logins x
    INNER JOIN durations y ON x.AccountId = y.AccountId AND x.UtcActionDate <= y.UtcActionDate
)
SELECT   AccountId
        ,CAST(UtcActionDate AS date) AS "Login Start Date"
        ,CAST(UtcActionDate AS time) AS "Login Start Time"
        ,Arg AS Duration
FROM both
WHERE UtcActionDate = max_login_date;