how to group by hour

[priyanthan]

I need to create a report with number of orders for each hour.
Basically I need to have a group for each hour for a particular day. The data for this report comes from the orders table, which looks as follows,

OrderID EmpID OrderDate OrderTime Amount

0001 6798 03/03/2009 06.30 25.50
0002 6798 03/03/2009 06.53 07.50

Purpose of this report is to find out the number of orders that were processed for each hour.

Can any one tell me how can I create group hour.

Working with CR8.5 & db2 database.

Thanks in advance.

 

[lbass]

What datatypes are your OrderDate and OrderTime fields?

-LB

 

[priyanthan]

both are CHAR.

one correction.
the date looks like 03032009 not like 03/03/2009

sorry for the confusion

 

[lbass]

Create a formula {@datetime} like this:

datetime(val(right({table.OrderDate},4)),val(left({table.OrderDate},2)),val(mid({table.OrderDate},3,2)),val(left({table.OrderTime},2)),val(mid({table.OrderTime},4,2),0)))

Then insert a group on this formula and choose "print on change of hour".

This assumes your date format is MM/dd/yyyy. You should always define that when it is not obvious from the sample datetimes.

-LB

 

[priyanthan]

Thanks a lot LB