Query help

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 14, 2012 at 4:47 pm

  #257949

  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.

• SQL_Starter

  Grasshopper

  Points: 15

  More actions

  March 14, 2012 at 5:01 pm

  #1459558

  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,...

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 14, 2012 at 5:02 pm

  #1459559

  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?

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  March 14, 2012 at 5:04 pm

  #1459561

  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

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 14, 2012 at 7:26 pm

  #1459573

  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.

• evelinel1f4r

  Valued Member

  Points: 60

  More actions

  March 14, 2012 at 8:41 pm

  #1459591

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

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 14, 2012 at 8:43 pm

  #1459594

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

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 14, 2012 at 9:05 pm

  #1459601

  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)

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 14, 2012 at 9:11 pm

  #1459603

  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.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 14, 2012 at 9:17 pm

  #1459605

  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?

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 14, 2012 at 9:19 pm

  #1459606

  Those days employee logged in but didn't logout.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 14, 2012 at 9:28 pm

  #1459607

  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

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 14, 2012 at 9:42 pm

  #1459615

  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.

• SQL_Surfer

  SSCrazy Eights

  Points: 8320

  More actions

  March 14, 2012 at 9:47 pm

  #1459616

  Yep. Thats it. Thanks for your help.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  March 14, 2012 at 9:49 pm

  #1459617

  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?