Multiple record evaluation

[IceRuby]

Crystal 10 – ODBC link to SQL 2000 database
Require a report to determine employees that did not receive pay rise via increment increase.

Employees are linked to classifications which determine their rate of pay and effective date value applied.

Report consists of two tables:- Payroll Transaction & Classification
Joined via right outer join Payroll Transaction (class inc) to Classification (class code)

During an increase the classification code remains the same however the hourly rate increases for the employee.

e.g.
CLASSIFICATION TABLE

CLASS EFFECTIVE DATE RATE
8033 1/1/05 $13.00
8033 5/8/05 $15.50

PAYROLL TRANSACTION TABLE

EMP NO. PERIOD PERIOD CLASS RATE
START DATE END DATE

Before rate change
8044 22/7/05 4/8/05 8033 $13.00

After rate change
8044 5/8/05 18/8/05 8033 $15.50

Therefore I require Crystal to compare the previous pay (period start date, class & rate) within payroll transaction file to the classification table (class, effective date) and return only employee records that have not had received rate increase.

Appreciate thoughts, all my attempts have failed to return correct result. Cannot seem to get Crystal to evaluate all criteria.

 

[Madawc]

Group them, for records within the date range. Is the minimum the same as the maximum? Suppress details and group header. Show group footer for those where maximum and minimum are equal.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10