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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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
June 12, 2023 at 8:02 pm
CREATE TABLE Timesheet
(user_id..,
etc, UNIQUE (user_ifd,login_timest),
login_timestamp DATTIME2(7) NOT NULL,
logout_timestamp DATTIME2(7),
CHECK(login_timestamp <=logout_timestamp),
);
The unit of work is a period of time which you are logged in. The duration is defined by a starting point in time and ending point. If the user is still logged on. When you do the query, then leaves a null model. The open end of the interval. This saves us having to kludge things with lead and lag, or even worse things. Too often a newbie will model this with a flag that assigns characteristic of a single temporal column. It mimics a clipboard for you. Check a box for in or out.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy