May 25, 2023 at 3:37 pm
I have a table which tracks of activity when users from one specific app login and logout. But I have seen some cases where let's say a user logins at 11:40 on March 24th and then logins again at 11:55 on the same day without logging out. The application doesn't allow that but I am trying to create a report for users who have logged in twice without logging out. The case I described would have been ok, if the user had logged out before 11:55 am before logging back in. Can someone help with the logic? All I need is a list of users who have logged in twice without logging out.
Select
action --This is where login and logout is captured.
,Name
,Login_Email_Address
,Date
from User_acticvity
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
May 25, 2023 at 4:57 pm
For a specific day, then:
Select
Name
,Login_Email_Address
,CAST(Date AS date) AS Date
from User_acticvity
Group By Name, Login_Email_Address, CAST(Date AS date) AS Date
Having Sum(Case When action = 'Login' Then 1 Else 0 End) >
Sum(Case When action = 'Logout' Then 1 Else 0 End)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 25, 2023 at 8:14 pm
This is another approach that will also work across days.
WITH ua AS
(
Select
[action] --This is where login and logout is captured.
,LAG([action]) OVER(PARTITION BY Login_Email_Address ORDER BY [Date]) AS Prev_Action
,[Name]
,Login_Email_Address
,[Date]
from User_acticvity
)
SELECT *
FROM ua
WHERE [action] = Prev_Action
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply