Query help

  • I have a table that has EmployeeID, EmployeeLoginDateTime and What JobFunction they logged in under. JobFunction is like Developing, Coding, Programming, Adminstering and LogOut.I need to write a query which pulls all the users and the day that they didn't log out.

    Any help would be approciated.

  • Compare the Login date and logout date using where clause.. To get the day out from the Login date use string functions.. I think it works,...

  • SQL_Surfer (3/14/2012)


    Any help would be approciated.

    Sure. But help us first!

    There is menu card in that hotel! I want you to buy me a lunch from that menu card! Can you?

  • SQL_Surfer (3/14/2012)


    I have a table that has EmployeeID, EmployeeLoginDateTime and What JobFunction they logged in under. JobFunction is like Developing, Coding, Programming, Adminstering and LogOut.I need to write a query which pulls all the users and the day that they didn't log out.

    Any help would be approciated.

    if they didnt log out...can we assume "LogOut" is Null?

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • If they forget to Logout, there won't be LogOut records. Or No record for the employee for that day with LogOut jobfunction. I have attached the sample data.

  • To get the day out from the Login date use string functions

  • Question is find out all the records for an employee where he didn't log out on a certain day.

  • This?

    DECLARE @TableA TABLE

    (

    EmployeeIDINT

    ,EmployeeLoginDateTimeDATETIME

    ,JobFunction VARCHAR(25)

    )

    INSERT @TableA

    SELECT 123,'2012-02-01 01:00:24','Coding'

    UNION ALL SELECT 123,'2012-02-01 3:00:00','Programming'

    UNION ALL SELECT 123,'2012-02-01 5:00:00' ,'LogOut'

    UNION ALL SELECT 234,'2012-02-01 01:00:24','Coding'

    UNION ALL SELECT 234,'2012-02-01 3:00:00','Programming'

    UNION ALL SELECT 234,'2012-02-01 5:00:00' ,'Coding'

    SELECT EmployeeID

    ,EmployeeLoginDateTime

    ,JobFunction

    FROM @TableA OutTab

    WHERE NOT EXISTS ( SELECT 1 FROM @TableA InrTab

    WHERE InrTab.JobFunction = 'LogOut'

    AND InrTab.EmployeeID = OutTab.EmployeeID)

  • Not quiet. Here, on 2/1, employee 234 didn't logout. So, it should just return 234, 2012/02/01, 'Din't LogOut'. This query is returning all other records but LogOut. I wanted just the ones where they didn't LogOut.

  • If there are 20 days that they did not logout, you need all of those 20 days? and u need it for all employees?

    Or u need those days that the employee signed in , but forgot to logout?

  • Those days employee logged in but didn't logout.

  • Then this?

    DECLARE @TableA TABLE

    (

    EmployeeIDINT

    ,EmployeeLoginDateTimeDATETIME

    ,JobFunction VARCHAR(25)

    )

    INSERT @TableA

    SELECT 123,'2012-02-01 01:00:24','Coding'

    UNION ALL SELECT 123,'2012-02-01 3:00:00','Programming'

    UNION ALL SELECT 123,'2012-02-01 5:00:00' ,'LogOut'

    UNION ALL SELECT 234,'2012-02-01 01:00:24','Coding'

    UNION ALL SELECT 234,'2012-02-01 3:00:00','Programming'

    UNION ALL SELECT 234,'2012-02-01 5:00:00' ,'Coding'

    UNION ALL SELECT 234,'2012-02-02 01:00:24','Coding'

    UNION ALL SELECT 234,'2012-02-02 3:00:00','Programming'

    UNION ALL SELECT 234,'2012-02-02 5:00:00' ,'Coding'

    UNION ALL SELECT 234,'2012-02-03 5:00:00' ,'Coding'

    UNION ALL SELECT 234,'2012-02-04 5:00:00' ,'Coding'

    UNION ALL SELECT 234,'2012-02-04 7:00:00' ,'Logout'

    UNION ALL SELECT 567,'2012-02-01 3:00:00','Programming'

    UNION ALL SELECT 567,'2012-02-01 5:00:00' ,'LogOut'

    UNION ALL SELECT 567,'2012-02-02 3:00:00','Programming'

    UNION ALL SELECT 789,'2012-02-01 3:00:00','Programming'

    SELECT EmployeeID

    ,CrsApp.EmployeeLoginDateTime

    ,'Missed LogOut' JobFunction

    FROM @TableA OutTab

    CROSS APPLY ( SELECT DATEADD(DD,DATEDIFF(DD,0,OutTab.EmployeeLoginDateTime),0) ) CrsApp (EmployeeLoginDateTime)

    WHERE NOT EXISTS

    ( SELECT 1

    FROM @TableA InrTab

    WHERE InrTab.JobFunction = 'LogOut'

    AND InrTab.EmployeeID = OutTab.EmployeeID

    AND DATEADD(DD,DATEDIFF(DD, 0 ,InrTab.EmployeeLoginDateTime),0) = CrsApp.EmployeeLoginDateTime)

    GROUP BY EmployeeID

    ,CrsApp.EmployeeLoginDateTime

  • Another solution

    ; WITH CTE AS

    (

    SELECT EmployeeID

    ,EmployeeLoginDateTime = DATEADD(DD,DATEDIFF(DD,0,OutTab.EmployeeLoginDateTime),0)

    ,JobFunction = CASE WHEN JobFunction = 'Logout' THEN 1

    ELSE 0

    END

    FROM @TableA OutTab

    )

    SELECT EmployeeID

    , EmployeeLoginDateTime

    , 'Missed LogOut' Comment

    FROM CTE

    GROUP BY EmployeeID , EmployeeLoginDateTime

    HAVING SUM(JobFunction) = 0

    This solution is definitely faster than the previous one!

    Table '#32AB8735'. Scan count 2, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 86 ms.

    Table '#32AB8735'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 65 ms.

  • Yep. Thats it. Thanks for your help.

  • SQL_Surfer (3/14/2012)


    Yep. Thats it. Thanks for your help.

    You're welcome.. One question - did u use the first one or the second?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply