Year from most recent transaction date

[blueboyz]

Crystal 9, Peachtree-Btrieve

I am creating a report on Employee's hours worked.
I need to group the employee's regular hours based on the year from their anniversary hire date. For example, if an employee was hired on March 16, 1999 and their last check was Feb. 14, 2007, I need to group the hours worked from March 16, 2006 to March 16, 2007. Is there a formula I can create to accomplish this?

I am already grouping the report by employee id, but I can't figure out how I can group each employee's hours based on their anniversary date.

 

[synapsevampire]

Well that's because that's not how grouping works.

What you want is to summarize their information based on their starting date.

So you'llneed formulas to do this, but please provide an example of what you intend to output.

-k

 

[MJRBIM]

As SV said you need to post an example of your data and the output you want to see -

I did something very similar from a view that returned all the data from a single table....

Name: {@AnnivesaryDateThisYear}
Formula:

Date (Year (CurrentDate),Month ({TABLE.HIRE_DATE}) ,Day ({TABLE.HIRE_DATE}) )
__________

Name: {@AnnDateChqDate}
Formula:

DateDiff ("d",{@AnnivesaryDateThisYear},{TABLE.CHQ_DATE} )
__________

Name: {@HoursWithinEmployeeYear}
Formula:

IF ({@AnnivesaryDateThisYear} > {TABLE.CHQ_DATE} AND {@AnnDateChqDate}>= -365 AND {@AnnDateChqDate}<= 365) THEN {TABLE.HOURS}
ELSE 0
__________


This works fine except for leap years that have 366 days.

 

[jagmb5555]

The original post suggests that he needs to provide annual hours worked between the original starting date being 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 of the second year and the next year's anniversary date on a recurring basis. 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. I.E Hire Date 02/01/1997 Anniversary Date - 01/31/1998 = report totals hours worked. Now list and total again starting 02/01/1998 to 01/31/1999 and repeating to current date.

 

[blueboyz]

Jagmb5555, your post is exactly what I am after. Is this possible?

 

[synapsevampire]

Curious thread. Shame the poster finds it too unappetizing or demanding to post an example of the required output.

Again, I don't know if you intend to show every year,and any other fields, percentages, etc. that might be required, which also may change the approach.

-k

 

[blueboyz]

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}

Hope this helps.