regarding date diff

[pooja]

how can I get the date diff from this table


id_no    state    time    process_code
-----------------------------------------

01         TX    12-01-09    1
02         TX    12-15-09    4
03         TX   12-21-09     4
03         AZ   10-01-09     1
05         NY   05-10-09     13
06         NY   11-12-12     21

how can I get time difference for (in days)

TX  from process_code 1 to process_code 4 (latest process code)

and

NY from process_code 13 to process_code 21


thank you so much in advance

[pooja]

 :o

[MFGF]

You could try the following:

Group on State.
Add two query calculations - Earliest Date - with the expression minimum([Time] for [State])
                                     - Latest Date - with the expression maximum([Time] for [State])

You could then work out the difference between these two calculated columns using a _days_between function in another calculation.

Regards,

MF.