Hours banding

[crystalnewbie]

Need to create an hourly labor report that looks like:

Hour | # of hours worked
8am - 9am | 2.0
9am - 10am | 1.0
10am - 11am | .5
etc.

given that I have the following information:

EmployeeName | PunchIn | PunchOut
John Doe | 3/1/04 8am | 3/1/04 11:30am
Jane Doe | 3/1/04 8am | 3/1/04 8:59am

Is it do-able?

Constraints are: Can't touch the back-end (ie. add table/views or sprocs)

 

[synapsevampire]

What consititues a complete hour?

You show Jane at 8:59 which resulted in an hour, you need to define the criteria for complete and partial hours, and while you're at it, share your version of Crystal and the database and connectivity used.

You might create variables for each hour period, and then use a formula in the details to iterate through each person for every hour in the day and create the sums based on whatever criteria constitues an hour, or half hour, etc.

whileprintingrecords;
numbervar 8hour;
numbervar 9hour;
numbervar 10hour;
etc...
numbervar x;
numbervar starthour := hour({table.punchin});
numbervar endhour := hour({table.punchout});
for x:=starthour to endhour do(
if x <> endhour then
(
if x = 8 then
(if x <> endhour then
8hour:=8hour+1
else
(
if minute(endhour) in 0 to 25 then
8hour:=8hour+.25
else
if minute(endhour) in 25 to 40 then
8hour:=8hour+.5
else
if minute(endhour) in 41 to 55 then
8hour:=8hour+.75
else
if minute(endhour) > 55 then
8hour:=8hour+1
)
etc...
)
)

Kinda fugly code, but I counldn't think of a more elegant fashion right now...

-k

 

[crystalnewbie]

A complete hour is from 00:00:00 to 00:59:59 (eg. 08:00:00 am to 08:59:59 am.

CR 8.5
MS SQL Server 2K
ODBC

 

[synapsevampire]

My point was that you didn't define the increments of an hour, you show .5, but what if the logout is 10:39?

Anyway, this solution should work for you, but there's probably a more elegant means were I to dwell on it.

-k