CODEWITHSUNDEEP
sql-serversql-server-2012
Kaspar
Kaspar

Reputation: 89

Log-in query in MSSQL

I have a question with SQL query that I can't figure out.
I would like to know the users that are currently logged in to the system. The data of the logging system is listed below.
My logic says that I would need to find Name that has Action_type=51 but doesn't have Action_type=52 or Action_type=53 following.

I've tried lots of different queries, but none of them got me the information that I need.
Here's my table:

Login table

|ID       |Action_type |        Date_time         | Name   |
|        1|51          |2017-11-06 17:13:55.227   | John   |
|        2|51          |2017-11-06 17:16:00.331   | Mary   |
|        3|53          |2017-11-06 17:45:55.227   | John   |
|        4|52          |2017-11-06 18:33:15.727   | Mary   |
|        5|51          |2017-11-07 10:13:49.558   | John   |
|        6|51          |2017-11-07 11:43:08.691   | Bonnie |

Action_types are defined by
51 - Log in
52 - Log out
53 - Log out(timed out)

////EDIT:

Select * From login_table lt
Where date_time = (Select Max(date_time)
             From login_table  
             Where action_type = action_type and action_type=51)

This is what I figured out, but this isn't exactly what I'm looking for, as it only returns the last one(max), but I still cant figure out how I can display all of the connect users right now.

Upvotes: 2

Views: 92

Answers (1)

Alex
Alex

Reputation: 5165

Use LEAD

Sample data:

CREATE TABLE #login_table( ID INT, Action_type INT, Date_time DATETIME, Name VARCHAR( 20 ))
INSERT INTO #login_table
VALUES
( 1,51,'2017-11-06 17:13:55.227', 'John   ' ),
( 2,51,'2017-11-06 17:16:00.331', 'Mary   ' ),
( 3,53,'2017-11-06 17:45:55.227', 'John   ' ),
( 4,52,'2017-11-06 18:33:15.727', 'Mary   ' ),
( 5,51,'2017-11-07 10:13:49.558', 'John   ' ),
( 6,51,'2017-11-07 11:43:08.691', 'Bonnie ' ),
( 6,53,'2017-11-09 11:43:08.691', 'Bonnie ' )

The query should look something like this:

SELECT *
FROM
(
    SELECT *, ( CASE WHEN LEAD(Action_type,1, 0) OVER( PARTITION BY Name ORDER BY Date_time ) IN( 52, 53 ) THEN 'LoggedOff' ELSE 'LoggedIn' END ) AS UserStatus
    FROM #login_table
) AS a
WHERE UserStatus = 'LoggedIn'
    AND Action_Type = 51
    -- Optional cut off filter
    -- AND Date_time > DATEADD( DAY, -1, GETDATE())

Output:

ID          Action_type Date_time               Name                 UserStatus
----------- ----------- ----------------------- -------------------- ----------
5           51          2017-11-07 10:13:49.557 John                 LoggedIn

Upvotes: 3

Related Questions