I have a SQL database I'm running this report from, and I have a problem I haven't run into yet:

When time is entered into the database there is a Job number, the amount of time being entered, and the type of the time (regular time, overtime, etc.)

I have a summary field which takes each employee and adds up the regular time, and a second summary adding up the overtime.

The catch is that when a mistake is made, they create a NEW job number and append a -01, or -02 if they make two mistakes.  So now instead of the job number being 12345, it is 12345-01, or 12345-02.  I sometimes even have 12345, 12345-01, 12345-02, and 12345-03.  What I need to do is only summarize the "latest" version of the job number.

 

My original thought was to take the full summary and subtract from it job numbers which had been superseded, but I haven't been able to figure out how to do that.  Anyone have any ideas as to how to make this work?  The original and the corrections are rarely sequential, ie there are usually several records in between them, and a random number at that.  I'm really a little lost on how to write a formula to deal with that.  Any help or pointer in the right direction would be very appreciated.

 create 2 rts

name = regular time

field to summarize = regular time field

type = sum

evalauate on change of group (select the job group)

reset= never

 

if you need it per employee add a group at the employee level and change the reset to on change of group (employee group)

 

repeat for OT usingt hat as the field to summarize

 

If there is some space, you can store the 'Previous' value in a shared variable just like the job_id.

 

HTH