>The query that you provided only get the records that have an RD record for a particular date and no matching RI record. I also need the RI records that match but the transaction date is greater than particular date.
>
>I have a table that records transations.
>
>When a part comes to our dock an RD(received to dock) transaction and date is recorded. When the part gets inspected an RI(received to inspection) transaction and date is recorded.
>
>So I need the number of parts that were in inspection on last Monday.
>
>I need all the parts that have an RD transaction record on or before the Monday date and that either has never been RI transaction or the RI transation date was after Monday date.
That's exactly what my query does. I'm not sure why it doesn't work for you.
SELECT * FROM mytable mt
WHERE TranDate < ?ldDate
AND EXISTS (SELECT 1 FROM mytable
WHERE OrderID = mt.OrderID and TranDate < ?ldDate AND TransactionCode = 'RD')
AND NOT EXISTS (SELECT 1 FROM mytable
WHERE OrderID = mt.OrderID and TranDate < ?ldDate AND TransactionCode = 'RI')
The part
WHERE OrderID = mt.OrderID and TranDate < ?ldDate AND TransactionCode = 'RI')
checks that there was no 'RI' transaction before specified date. It doesn't matter if there was one after.
--sb--