sqlsql-serversql-updatevariable-assignmentdatediff

Creating a new variable based on condition SQL


I am working on a project and have a table with the following data:

date time username type session_time
2023-08-28 07:56:50.0000000 aavery35 login NULL
2023-08-28 08:07:53.0000000 aavery35 view NULL
2023-08-28 08:08:48.0000000 aavery35 update NULL
2023-08-28 08:08:48.0000000 aavery35 update NULL
2023-08-28 08:08:48.0000000 aavery35 update NULL

I am trying to find a way to update the session_time variable to find where date = date and username = username then subtract logout:time - login:time

part of the problem is that every user does not necessarily have a logout time. In that instance i would like to go to their last action of that day and create a new row for that date, time, and username and create a logout instance.

I don't think this is possible with all the 'type' variables being in the same column. So instead i have created three new variables login_time, logout_time, session_time. I then ran the following code :

Alter Table ##remote_users
Add session_time date;

Alter Table ##remote_users
Add login_time time;

Alter Table ##remote_users
Add logout_time time;

Update ##remote_users
set login_time = time
where username = username AND date = date AND Type = 'login';

Update ##remote_users
set logout_time = time
where username = username AND date = date AND Type = 'logout';

Update ##remote_users
set session_time = login_time - logout_time
where username = username AND date = date;

select date, time, username, type, login_time, logout_time
from ##remote_users
WHERE type = 'login' OR type = 'logout' OR type = 'sessiontimeout'
order by username 

This was close because it created the variables that i needed but the session_time update did not work it gave the following error:

Msg 8117, Level 16, State 1, Line 57
Operand data type time is invalid for subtract operator.

Additionally, users without a logout time get a null value for teh variable logout_time.


Solution

  • You can do something like this:

    select cast(date as date) as date, cast(time as time) as time, type, cast(session_time as datetime) as session_time, username
    ,   CAST(NULL AS TIME) AS login_time
    ,   CAST(NULL AS TIME) AS logout_time
    into #remote_users
    from 
    (
        VALUES  (N'2023-08-28', N'07:56:50.0000000', N'aavery35', N'login', NULL)
        ,   (N'2023-08-28', N'08:07:53.0000000', N'aavery35', N'view', NULL)
        ,   (N'2023-08-28', N'08:08:48.0000000', N'aavery35', N'update', NULL)
        ,   (N'2023-08-28', N'08:08:48.0000000', N'aavery35', N'update', NULL)
        ,   (N'2023-08-28', N'17:08:48.0000000', N'aavery35', N'logout', NULL)
        ,   (N'2023-08-29', N'03:08:48.0000000', N'aavery35', N'login', NULL)
        ,   (N'2023-08-29', N'08:08:48.0000000', N'aavery35', N'update', NULL)
        ,   (N'2023-08-30', N'08:08:48.0000000', N'aavery35', N'login', NULL)
        ,   (N'2023-08-30', N'10:08:48.0000000', N'aavery35', N'logout', NULL)
        ,   (N'2023-08-30', N'13:08:48.0000000', N'aavery35', N'secret', NULL)
    ) t (date,time,username,type,session_time)
    
    -- #1 Last time version
    update  t
    set login_time = loginTimeNew
    ,   logout_time = t.logoutTimeNew
    ,   session_time = CAST(dateadd(ms, datediff(ms, logintimenew, logouttimenew) , '19000101') AS TIME)
    FROM    (
        select  min(time) OVER(PARTITION BY username, date) as loginTimeNew
        ,   Max(time) OVER(PARTITION BY username, date) as logoutTimeNew
        ,   *
        FROM    #remote_users t
        ) t
    
    
    -- #2 "Real" times version
    update  t
    set login_time = loginTimeNew
    ,   logout_time = t.logoutTimeNew
    ,   session_time = CAST(dateadd(ms, datediff(ms, ISNULL(loginTimeRealNew,logintimenew), ISNULL(logoutRealTimeNew,logouttimenew)) , '19000101') AS TIME)
    FROM    (
        select  min(time) OVER(PARTITION BY username,date) as loginTimeNew
        ,   Max(time) OVER(PARTITION BY username,date) as logoutTimeNew
        ,   min(case when type = 'login' then time end) OVER(PARTITION BY username, date) as loginTimeRealNew
        ,   Max(case when type = 'logout' then time end) OVER(PARTITION BY username, date) as logoutRealTimeNew
        ,   *
        FROM    #remote_users t
        ) t
    

    I wrote two versions, #1 is the simple one that takes min and max time to create logintimeNew and logoutTimeNew columns, and then does a time diff manipulation to create the "time" part of a session time.

    This assumes that first date per day is login, and last day if logout. If you really want to find the "session time" parts, you can use the #2 version. That one does a conditional aggregation to find login / logout rows, and falls back to first / last rows if those are missing

    To explain the details:

    min(time) OVER(PARTITION BY username, date) is a window aggregation that calculates minumum time grouped (partitioned) by user and date. This helps us get the needed date values per day.

    CAST(dateadd(ms, datediff(ms, logintimenew, logouttimenew) , '19000101') AS TIME) this complex formula is to convert difference of two days in terms of "time" datatype. There's probably a better way, but this version calculates difference in milliseconds and then creates a date by adding milliseconds to the "zero date in sql server", which is 19000101. Then, by converting this to TIME, one gets the time part of the difference.