Group Based on Custom Pay Period Week

[vanessa03]

Crystal 2011- SQL 2008 R2
I have detailed payroll records with time in and time out for each employee. I need to be able to total up hours by Reg Time and OT by week by quarter. The problem is my week starts Sunday's at 7:00am, so if an employee punches in Saturday night at midnight and punches out at 9:00am on Sunday morning, then that time needs to be broken up into 2 pay-periods - 6 hours for one and 2 hours for the next. I know how to get the time worked for each detailed record. I am just having a problem on how to group into weeks. Right now I have the records sorted by Last Name, Employee ID and time in. I am prompting for beginning date and ending date for selection. Any help would be greatly appreciated. Thanks.

 

[Madawc]

If you can write an SQL procedure and know basic SQL, you could split records into two if they fall across one of your weeks.

Failing that, you'll need to do a Mock Crosstab. A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5 or Crystal 2008, you can also duplicate formula fields using the Field Explorer. I'd suppose it is the same in Crystal 2011.


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[vanessa03]

That's what I was afraid of. Unfortunately this is a MYSql Database for TimeIPS software. I have read rights only to it. I guess I will have to do the mock crosstab. Thanks for responding.