Need help with a Query logic

  • 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]

  • 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".

  • 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

  • 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