find late entry employees - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

find late entry employees

Author

Topic

hussainzim
Starting Member

21 Posts

Posted - 2007-11-15 : 00:06:24

i want to find employees who has a Entry Time greater than 9.00 AM thrice per month(current month in the system).

Table Structure
Attendance(AttId,EmployeeId,InDate,TimeIn,TimeOut,OutDate)
thank you in advance

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 00:20:45

http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx
http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

Select EmployeeID,Count(*)
FROM Attendance
Where Hour(Timein) >= 9 AND Minute(TimeIn) > 0
Having Count(*) >=3

Poor planning on your part does not constitute an emergency on my part.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 00:22:59

You can add the datein column and also filter for parts of that month(datein) = month(getdate()) for example

Read the links I posted

Poor planning on your part does not constitute an emergency on my part.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-15 : 04:54:16

quote:

Select EmployeeID,Count(*)
FROM Attendance
Where Hour(Timein) >= 9 AND Minute(TimeIn) > 0
Having Count(*) >=3

There are a couple of issues here...
There is a HAVING clause with no GROUP BY!
The highlighted clause needs some rethinking too... If the TimeIn = any exact hour (e.g 10:00AM) it will not be picked up.
[CODE]
SELECT employeeID
, Count(*)
FROM Attendance
WHERE Hour(DateAdd(ss, -1, TimeIn)) >= 9
GROUP
BY employeeID
HAVING Count(*) >= 3
[/CODE]

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 07:50:13

quote:
Originally posted by georgev

quote:

Select EmployeeID,Count(*)
FROM Attendance
Where Hour(Timein) >= 9 AND Minute(TimeIn) > 0
Having Count(*) >=3

There are a couple of issues here...
There is a HAVING clause with no GROUP BY!
The highlighted clause needs some rethinking too... If the TimeIn = any exact hour (e.g 10:00AM) it will not be picked up.
[CODE]
SELECT employeeID
, Count(*)
FROM Attendance
WHERE Hour(DateAdd(ss, -1, TimeIn)) >= 9
GROUP
BY employeeID
HAVING Count(*) >= 3
[/CODE]

Well I wasn't going to do ALL the work for him..

Poor planning on your part does not constitute an emergency on my part.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-15 : 08:06:20

quote:

Well I wasn't going to do ALL the work for him..

Apologies!
I found this problem an interesting one, especially because of the trick involved to return the correct resultset... So I just had to give it a go myself :D

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 12:36:50

quote:
Originally posted by georgev

quote:

Well I wasn't going to do ALL the work for him..

Apologies!
I found this problem an interesting one, especially because of the trick involved to return the correct resultset... So I just had to give it a go myself :D

No worries. When someone spends more time typing a post then doing a simple search of BOL or this site for date related functions (like in a post I answered for him not minutes before), I like to give them a "hint" and an unworking query just for kicks. I have to amuse myself somehow. If he insists on wasting my time, i will return the favor! LOL

Its all good

Poor planning on your part does not constitute an emergency on my part.

georgev
Posting Yak Master

122 Posts

Posted - 2007-11-16 : 07:30:33

I have to admit... I like your style!

George
<3Engaged!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-11-16 : 12:45:20

quote:
Originally posted by hussainzim

i want to find employees who has a Entry Time greater than 9.00 AM thrice per month(current month in the system).

I'd be screwed if my company did this! It's 9:45am over here and I just barely got in.

And no I don't have anything useful to add to this topic, just needed to amuse myself this morning.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-11-16 : 14:43:38

oh tara that's nothing... i regularly come in at 10:30 a.m

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-11-16 : 19:37:31

I find it depressing that anyone would help the OP with such a snooping, tattletale type query!