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