Group based on hourly datetime

I have a report field that shows clock in and out for an employee. For example,

Date Classification Name
8/1/2006 6:30:26am IN A
8/1/2006 3:04:15PM OUT A
8/1/2006 7:30:26am IN B
8/1/2006 3:04:15PM OUT B

and so on...

I would like to have my report to show employees that were here from 7:00am -8:00am, 8:00am-9:00am, etc...

So my report would look like:
6:00AM-7:00AM
A
7:00AM-8:00AM
A
B

8:00AM-9:00AM
A
B
9:00AM-10:00AM
A
B

I'm not sure how to create time/hourly group and how i would achieve this. Please help!! Thanks,create a formula to extract hour
and then group by that formula

datepart('h',datetimefield)|||I created the formula:

datepart('h',{EmployeeClocking.WhenCreated})

and then grouped it on that forumula but it says:

The formula result must be a string!!

Thanks,|||That formula worked. But my results are not what I expected. For example, table:

Date Classification Name
8/1/2006 6:30:26am IN A
8/1/2006 3:04:15PM OUT A
8/1/2006 7:30:26am IN B
8/1/2006 3:04:15PM OUT B

gives me report:

6:00AM
A
7:00AM
B (I should get A because A worked until 3pm, but I get the ones that clocked in or out)
3PM
A
B|||You've not replied to my post on the other forum where you posted this question. :)

It was a simple request for what database you are running the report against, but I'll expand on why here.

If you use the clock in/out times to drive the report then you will only get hour intervals reported when someone actually clocks in/out within that hour, which is why you only got

6:00AM
A
7:00AM
B (I should get A because A worked until 3pm, but I get the ones that clocked in or out)
3PM
A
B

As you wrote '7:00am -8:00am, 8:00am-9:00am, etc...' I think you want output something like this instead:

6AM - 7AM
A
7AM - 8AM
A
B
9AM - 10AM
A
B
10AM - 11AM
A
B

...

3PM - 4PM
A
B

where you display all hours from the first clock in to the last clock out.

In which case I think you are going to need to generate a report with at least 24 rows (24 hours per day!) and then run a subreport for each hour interval to display those employees who clocked in before/during that hour and clocked out during/after it, on any given day.
There will be a slight complication if the clock in / out crosses the midnight boundary, but this can be overcome if necessary.

The 'problem' here is generating a report with at least 24 rows, preferrably exactly 24 rows, but not too many more than 24 rows. Which is why I asked what database you are running on.|||I'm running on SQL Server. I really need helplwith this coz I tried several things and it's not working. How would I do:

"In which case I think you are going to need to generate a report with at least 24 rows (24 hours per day!) and then run a subreport for each hour interval to display those employees who clocked in before/during that hour and clocked out during/after it, on any given day.
There will be a slight complication if the clock in / out crosses the midnight boundary, but this can be overcome if necessary.

The 'problem' here is generating a report with at least 24 rows, preferrably exactly 24 rows, but not too many more than 24 rows. Which is why I asked what database you are running on."

Thank you so much for your help.|||A report with 24 rows:

a) In Oracle, I might have written an 'Add Command' query in CR to get the first 24 rows from all_objects, using rownum. Can a similar thing be done in SQL Server?
b) create a specific table for this purpose with 24 rows of anything in it.
c) Use a data table that you know will always have at least 24 rows of data.

c's not the best one 'cos you'd need to suppress all records after the 24th one, and I think the subreport would still be run for all the extra records even though the detail is suppressed.

In the main report, create a formula to make a datetime out of the record number, something like
dateadd('h', recordnumber -1, today)

Add something from your main query to the supressed header section. (Anywhere really, it's just got to be used for the report to do anything.)
Create a subreport in the details and pass the formula to it as a parameter.
In the subreport, select the day's data from the employee clocking table with a formula like

// restrict to one day
date({EmployeeClocking.WhenCreated}) = date({?Pm-@.hour})
and
// clocked in before/during the hour
( {EmployeeClocking.Classification = 'IN'
and {EmployeeClocking.WhenCreated} < dateadd('h', 1, {?Pm-@.hour})
)
and
// clocked out during/after the hour
( {EmployeeClocking.Classification = 'OUT'
and {EmployeeClocking.WhenCreated} >= {?Pm-@.hour}
)

Then you should just need to fiddle with the format of the subreport and (back in the main report) the suppress blank sections / suppress blank subreport options etc. to get the format you want.

Note that the logic of your record selection might need tweeking. For instance, what if they clock in/out twice in a day or over a midnight boundary? Are your clock in/out records linked to pair them together? Can someone forget to clock in or out?