help calculating the time difference between two records based on a 3rd field value

[rhart36]

I need help calculating the time difference between two records based on a 3rd field value. For instance, I need to calculate the time difference in the below table when the status equals WAPPR and COMP. Please note these are different rows within a table. I will also need calculate the time difference between other Status changes.

Table:
WONUM STATUS CHANGEDATE
TKT9904 COMP 10/16/2014 7:59
TKT9904 REVIEW 10/16/2014 7:59
TKT9904 APPR 10/14/2014 9:31
TKT9904 WAPPR 10/13/2014 11:19
TKT9904 INPRG 10/15/2014 8:01
TKT9904 AUTH 10/13/2014 12:16
TKT9904 SCHED 10/13/2014 12:16
TKT9904 AUTH 10/13/2014 12:17
TKT9904 ASSESS 10/13/2014 11:39

Any Assistance would be greatly appreciated!!

 

[SkipVought]

hi,

I need to calculate the time difference in the below table when the status equals WAPPR and COMP

You never have a row "when the status equals WAPPR and COMP".

So do you really mean that you want to calculate the time difference in the below table between the row with the status of WAPPR and the row with the status of COMP?

And then pleasse clarify your next request, "calculate the time difference between other Status changes."

Please post what your expected results would be as well.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rhart36]

Total elapsed time: = COMP 2014-10-16 07:59 - WAPPR 2014-10-13 11:19
2day 20hours 40minutes

 

[SkipVought]

Waiting..........

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[rhart36]

posted the Calculation: Total elapsed time: = COMP 2014-10-16 07:59 - WAPPR 2014-10-13 11:19

 

[SkipVought]

Still waiting......

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BobSuruncle]

So you have a COMP/WAPPR pair?
What other pairings do you have that you need to calculate time differences for?
Does COMP *ALWAYS* come before WAPPR?
Would there ever be a WONUM without one or the other of these statuses?
Would there ever be a repeat of one of these statuses for a given WONUM?
Is the CHANGEDATE field a string or a DateTime field?

Without complete information, it's difficult to help you find a solution.

Off the top of my head, it looks like you need to group on WONUM.
If CHANGEDATE is a string, then you'd need to convert it to DateTime.
There are a few ways of approaching this, but they depend on the answers to the questions above.
One way would be to create 3 formulas - one in the WONUM Group Header, one in the Details, and one in the WONUM Group Footer.
The one in the Group Header would reset some Global DateTime variables.
The one in the Details would store the DateTime values for the appropriate statuses.
The one in the Group Footer would calculate the difference and display that to the screen.



Bob Suruncle