Alan, This requires two references to the table, each image getting a separate correlation name so that your WHERE conditions can tell the images apart:
SELECT tranid FROM tranactions t1 + WHERE t1.tranaction = 'WO Completed' + AND EXISTS + (SELECT tranID FROM tranactions t2 + WHERE t2.tranaction = 'WO Approved' + AND t1.tranactiondts < t2.tranactiondts) Bill On 30 Apr 2004 at 14:16, Alan Wolfe wrote: > Hi everyone, > > I have something I'm trying to find the sql for but haven't so far > been able to do it. > > we have a table called tranactions. it has 3 columns: > > tranid integer > tranaction text 14 > tranactiondts datetime > > heres an example of this table's data: > > 1234, 'Submit WO',04/29/2004 12:31:45 > 1234, 'WO Completed', 04/29/2004 02:00:00 > 1234, 'WO Approved',04/30/2004 06:46:44 > 5678, 'Submit WO', 04/30/2004 12:48:46 > 5678, 'WO Approved', 04/30/2004 2:13:36 > 5678, 'WO Completed', 04/30/2004 3:14:42 > > What I'm trying to do is find out which tranids have a "WO Completed" > that comes before the "WO Approved" comparing tranactiondts. > > so, for the above data, it would report that 1234 had "WO Completed" > before it had "WO Approved", but it wouldnt report 5678 because > completed comes after approved. > > Anyone have any idea how to do this? > > Thank you! > Alan
