Unfeasibly hard report

[mp9]

I'm trying to report (using CR10) on an Oracle database table which has this structure:

PersonID
Status
StartTime
EndTime

So far, so straightforward. Now a person can go through a four stage process:

Stage 1 - Idle, indicated by Status codes of I (idle) or O (off)
Stage 2 - work assigned, Status code of AS
Stage 3 - work agreed, Status code of AG
Stage 4 - working, Status code of W

Now I need to report the time taken between the start of Stage 3 to the start of Stage 4. Easy enough, right? Except I only need to do this where each process loop began with a status code of I, and exclude those process loops which began with a status code of O.

How can I do this?!

THanks in advance for any suggestions.

http://www22.brinkster.com/accessory/

 

[lbass]

Create a formula {@I}:

if {table.staus} = "I" then 1

Then use a group selection of:

sum({@I},{table.personID}) = 0

Then you can use a formula like this:

if {table.status} = "W" then
datediff("s",previous({table.starttime}), {table.starttime})

This would return the difference in seconds. This assumes that the codes are entered sequentially and that all person IDs have the AG and W codes.

Because of the group selection if you intend to do any calculations across personIDs, you would need to use running totals or variables, instead of inserted summaries.

-LB