Count Employees in time period

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

General SQL Server Forums

New to SQL Server Programming

Count Employees in time period

Author

Topic

lgarcia3
Starting Member

15 Posts

Posted - 2011-05-12 : 18:21:01

I need help with a query. I have a table with information about employees. The table has information of clock in time, clock out time, and position type of the employee. Now I need to count how many employees of position type X where present between time t1 and time t2. The table looks something like this:

EmpID, ClockInDate, ClockInTime, ClockOutDate, ClockOutTime, EmployeePosition
1, 1/15/2011,6:00,1/15/2011,14:00,Server1
2, 1/15/2011,6:00,1/15/2011,13:30,Server1
3, 1/15/2011,6:00,1/15/2011,14:30,Cook

So, I want my query to tell me:

EmployeePosition, Quantity, TimeActive
Server1, 2, 6:00 - 13:30
Cook, 1, 6:00 - 14:30

Is it possible to get this in one query?

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-12 : 20:32:48

If you want to determine how many employees of each position where present at any given point in time, that is easy enough. However, what you are asking for does not seem like a well-defined problem. For example, if the data were like what I am showing below, what should your output look like?


1   1/15/2011   6:00   1/15/2011    7:00   Server1
2   1/15/2011   8:00   1/15/2011    9:00   Server1
3   1/15/2011  10:00   1/15/2011   11:00   Server1

lgarcia3
Starting Member

15 Posts

Posted - 2011-05-13 : 07:32:01

Thanks for your response!
Yes, that is exactly what I am asking for. I need to know how many employees of each position were present at any given point in time, specially when the time overlaps. The result for the data you mentioned should look like:

1/15/2011 Server1 1
1/15/2011 Server1 1
1/15/2011 Server1 1

Because the time they are there never overlaps. If it did, say the data looks like:

1 1/15/2011 6:00 1/15/2011 10:00 Server1
2 1/15/2011 6:00 1/15/2011 10:00 Server1
3 1/15/2011 10:00 1/15/2011 11:00 Server1

then it would look like:

1/15/2011 Server1 2
1/15/2011 Server1 1

What is important is to know how many employees of one class are present at certain time.
Again thanks for your response.

lgarcia3
Starting Member

15 Posts

Posted - 2011-05-16 : 12:42:23

So, anyone has any ideas about this? thanks!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-16 : 13:19:27

Isn't (the business) as simple as selecting rows where the clockin time is earlier than your input datetime and the clockout time is later?
Is your problem caused by the fact that you don't have a simple DATETIME column as your clockin? What are the datatypes of your date/time columns?

Be One with the Optimizer
TG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-16 : 13:23:07

I would have liked to respond, but the problem is still not clear to me - and I suspect the same for other people who may be trying to respond. As I had said in my previous post, if you are looking for the count at a given point in time, like:

"at 10:30 AM, tell me how many of each kind"
or "I want to know how many of each kind at every half hour interval",
or even, "I want to know how many of each kind when any employee checks in or checks out",

all of that is possible. But the way you are describing it - not clear to me what you are looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources