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 4:47 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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.
Post #1267153
Posted Wednesday, March 14, 2012 5:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 29, 2012 4:45 PM
Points: 3, Visits: 38
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,...
Post #1267158
Posted Wednesday, March 14, 2012 5:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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?
Post #1267159
Posted Wednesday, March 14, 2012 5:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:08 PM
Points: 1,912, Visits: 19,381
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
Post #1267161
Posted Wednesday, March 14, 2012 7:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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.

  Post Attachments 
SampleData.txt (4 views, 342 bytes)
Post #1267177
Posted Wednesday, March 14, 2012 8:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 14, 2012 8:39 PM
Points: 6, Visits: 1
To get the day out from the Login date use string functions
Post #1267198
Posted Wednesday, March 14, 2012 8:43 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
Question is find out all the records for an employee where he didn't log out on a certain day.
Post #1267201
Posted Wednesday, March 14, 2012 9:05 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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'


SELECT EmployeeID
,EmployeeLoginDateTime
,JobFunction
FROM @TableA OutTab
WHERE NOT EXISTS ( SELECT 1 FROM @TableA InrTab
WHERE InrTab.JobFunction = 'LogOut'
AND InrTab.EmployeeID = OutTab.EmployeeID)

Post #1267209
Posted Wednesday, March 14, 2012 9:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:50 AM
Points: 329, Visits: 858
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.
Post #1267211
Posted Wednesday, March 14, 2012 9:17 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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?
Post #1267213
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse