SQL How Many Employees Are Working, Group By Hour

Question

I have a table, a timetable, with check-in and check-out times of the employees:

ID    Date     Check-in     Check out
1     1-1-2011 11:00        18:00
2     1-1-2011 11:00        19:00
3     1-1-2011 16:00        18:30
4     1-1-2011 17:00        20:00

Now I want to know how many employees are working, every (half) hour. The result I want to see:

Hour  Count
11    2
12    2
13    2
14    2
15    2
16    3
17    3
18    2,5
19    1

Every 'Hour' you must read as 'till the next full hour', ex. 11 -> 11:00 - 12:00

Any ideas?

Uri Goren · Answer 1 · 2011-12-30T02:30:23.597

0

Build an additional table, called Hours, containing the following data:

 h
 00:00
 00:30
 01:00
 ...
 23:30

then, run

Select h as 'hour' ,count(ID) as 'count' from timetable,hours where [Check_in]<=h and h<=[Check_out] group by h

edited Dec 30 '11 at 02:30

answered Dec 30 '11 at 01:17

Uri Goren

13,386
6
58
110

The "h as 'hour'" part is backwards, and the group-by is missing on the end. – Burleigh Bear Dec 30 '11 at 02:16
These are usually double-quotes, not single ones: `Select h as "hour"` – ypercubeᵀᴹ Dec 30 '11 at 02:52

SQL How Many Employees Are Working, Group By Hour

1 Answers1

Linked