Creating a new variable based on condition SQL

Question

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.

Answer 1

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.