Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Query help Expand / Collapse
Author
Message
Posted Wednesday, March 14, 2012 9:19 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
Those days employee logged in but didn't logout.
Post #1267214
Posted Wednesday, March 14, 2012 9:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
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

Post #1267216
Posted Wednesday, March 14, 2012 9:42 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
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.
Post #1267224
Posted Wednesday, March 14, 2012 9:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
Yep. Thats it. Thanks for your help.
Post #1267225
Posted Wednesday, March 14, 2012 9:49 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
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?
Post #1267226
Posted Wednesday, March 14, 2012 9:51 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
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!!!
Post #1267228
Posted Thursday, March 15, 2012 7:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
OK. What if employee's shift starts around 11PM and goes all the way till next morning. How would that change the query?
Post #1267980
Posted Thursday, March 15, 2012 10:24 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
That query willl not handle that scenario. We can work it out anyways. Do u have any "Shift identifier"??
Post #1268010
Posted Friday, March 16, 2012 12:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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)
Post #1268033
Posted Friday, March 16, 2012 7:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
No, we don't have shift identifier.
Post #1268240
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse