|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 209,
Visits: 514
|
|
| Those days employee logged in but didn't logout.
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248,
Visits: 5,352
|
|
Then this?
DECLARE @TableA TABLE ( EmployeeID INT ,EmployeeLoginDateTime DATETIME ,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
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248,
Visits: 5,352
|
|
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.
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 209,
Visits: 514
|
|
| Yep. Thats it. Thanks for your help.
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248,
Visits: 5,352
|
|
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?
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 209,
Visits: 514
|
|
| Ran the first one and got the result I was expecting. Since, you mentioned 2nd one was effecient, I will use the second one. Thanks again!!!
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 209,
Visits: 514
|
|
| OK. What if employee's shift starts around 11PM and goes all the way till next morning. How would that change the query?
|
|
|
|
|
SSCrazy
      
Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248,
Visits: 5,352
|
|
| That query willl not handle that scenario. We can work it out anyways. Do u have any "Shift identifier"??
|
|
|
|
|
SSC Rookie
      
Group: General Forum Members
Last Login: Wednesday, April 10, 2013 1:07 PM
Points: 26,
Visits: 88
|
|
Try this query
select employeeid, employeelogindatetime, employeedept from tablename where employeelogindatetime between @fromdate and @todate and employeelogouttime like isnull('',null)
|
|
|
|
|
SSC Veteran
      
Group: General Forum Members
Last Login: Yesterday @ 7:01 AM
Points: 209,
Visits: 514
|
|
| No, we don't have shift identifier.
|
|
|
|