building expression comparing date fields

[endoflux]

Gentlemen--

I have a table of records (DEFECT) left-joined to another table (DTEVTS); DTEVTS holds multiple records for each record in DEFECT. The records in DTEVTS are events descirbing the status of a work order-- open, closed, etc.

Ultimately, i want to end up with a flat file holding all the information i need for each record in a single line item (opened date, closed date, etc), but I have a small problem where DTEVTS has a "Closed" event and a "Reopened" event; I can't simply look for a "closed" event and pull that as my "closed date" because it may have been reopened later...

(defect.Record) 111
(dtevts.eventtype)opened (dtevts.date)1/1/2005
(dtevts.eventtype)closed (dtevts.date)1/2/2005
(dtevts.eventtype)reopened (dtevts.date)1/3/2005
(dtevts.eventtype)closed (dtevts.date)1/4/2005

In this example, my closed date would show up as 1/4/2005-- all is well. However...

(defect.Record) 111
(dtevts.eventtype)opened (dtevts.date)1/1/2005
(dtevts.eventtype)closed (dtevts.date)1/2/2005
(dtevts.eventtype)reopened (dtevts.date)1/3/2005

...this work order is actually open, since it was reopened, yet my current code would show a "closed date" of 1/2/2005.

I need to somehow compare the dates of the "reopened" events with the dates of the "closed" events and only count a "closed event" if it's date is greater than any "reopened event". Ideas? Thanks!

 

[PHV]

yet my current code
Which code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[endoflux]

my current code" refers to me creating a make-table query that returns records with "closed dates" and taking the MAX of those dates grouped by each defect.recordnumber...it tells me the last closed date, but not if it was reopened after that date-- sorry for the omission; that's probably useful info

Thanks!

 

[PHV]

You may consider playing with two "last date" field:
LastClosed: Max(IIf(dtevts.eventtype='closed',dtevts.date,Null))
LastEvent: Max(dtevts.date)

And then the defect.Record is closed if both dates are equal.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[endoflux]

Unfortunately, other things can be edited on the work order after it's "closed"; there are more statuses involved than I've included in the scope here: For example, a work order can be "closed" and then a quality investigation may start, and every time a change is made, it will be logged in DTEVTS...

 

[PHV]

A partially exposed problem is likely to not get a satisfactory solution ...
You may play with something this:
LastClosed: Max(IIf(dtevts.eventtype='closed',dtevts.date,Null))
LastOpened: Max(IIf(dtevts.eventtype Like '*opened',dtevts.date,Null))

And then the defect.Record is closed if LastClosed > LastOpened

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[endoflux]

Sweet! That will work, I believe...I just have to put all the status numbers (each status is keyed off numbers 1-16) that I consider "open" in the LastOpened expression, and the same for "Closed".

One more favor, please-- can you help with how to write the expression for the Last Closed field if statuses 8, 10, and 11 mean that it's closed, would it be something like this?:

LastClosed:
Max(IIf(dtevts.eventtype in(8, 10, 11),dtevts.date,Null))

Thanks again!