Payroll - Annual totals for hours worked

[jagmb5555]

I need to provide annual hours worked between the Employee Hire Date and the Employee first year Anniversary Date and then list recurring one year totals as each year passes based on the Anniversary Date. Year 1 total hours worked starting from hire date to anniversary date = Total Hours Worked. Next total would be from Year 2 Anniversary date to the end of the 2nd fiscal year = Total Hours worked for the new one year period and so forth to current date. So in short annual summary of hours worked based on a fiscal year using the employee's hire date
Here's an example:

Employee: Kathy
Hire Date: 3-16-1999
Received the following checks
3-20-99 reg hours: 80
4-4-99 reg hours: 80
5-4-99 reg hours: 80
2-10-01 reg hours: 80
2-24-01 reg hours: 80
12-10-01 reg hours: 80
1-31-02 reg hours: 80
3-10-02 reg hours: 80
4-10-02 reg hours: 80
12-15-02 reg hours: 80
todays date is 12-31-02

Need the data to be grouped by the employee's anniversary date:

Kathy
3-16-99 to 3-15-00
240 hours

3-16-00 to 3-15-01
160 hours

3-16-01 to 3-15-02
240 hours

Employee: Dan
Hire Date: 6-1-00
Received the following checks
7-1-00 reg hours: 80
8-4-00 reg hours: 80
9-4-00 reg hours: 80
3-10-01 reg hours: 80
5-24-01 reg hours: 80
9-10-01 reg hours: 80
1-31-02 reg hours: 80
5-10-02 reg hours: 80
6-10-02 reg hours: 80
8-15-02 reg hours: 80
todays date is 12-31-02

Dan
6-1-00 to 5-31-01
400 hours

6-1-01 to 5-31-02
240 hours

6-1-02 to 5-31-03
160 hours

The hire date is stored in the table {Employee.HireDate}
The payroll hours are stored in another table {Journal.PRHours}
The payroll check dates are stored in the same table as the hours {Journal.TransactionDate}

My problem is creating a formula that I can use to group and total the annual hours worked. I am using Crystal 9.0 and Peachtree Accounting 2007.

 

[synapsevampire]

I think I responded to this one before.

Here are some considerations:

This is not called grouping, it's called summing data. Grouping is a global term applied to data, and since each employee starts on different dates, your continued misuse of the term will confuse people, and makes me leary of attempting to help you with advanced requirements.

Second, you state "Year 1 total hours worked starting from hire date to anniversary date = Total Hours Worked. Next total would be from Year 2 Anniversary date to the end of the 2nd fiscal year".

So what happens to the data between the end of the 2nd fical year and the start of the next hire date, you ignore it? Or are you also misusing the term fiscal year? Your example doesn't show any use of a fiscal date, so I'll assume you think a fiscal date means an anniversary date, which obviously isn't the same thing.

Most summaries would occur during the pass of the data, rather than showing all details, and then showing all yearly summaries, which is more complex, you intend to show all details, and then pass through the data again to build up annual summaries. A shame it isn't like this:

Employee: Kathy
Hire Date: 3-16-1999
Received the following checks
3-20-99 reg hours: 80
4-4-99 reg hours: 80
5-4-99 reg hours: 80
3-16-99 to 3-15-00
240 hours
...etc...

That's logical because you can summarize as you pass through each year.

So in your case, you'll require variables and a subreport to accomplish this, but I'd like clarification of all of the above points before I delve into this, it's not a quick thing to work out.

-k