1.yes it can be reprocessed multiple times but rarely happens
2.yes it's date datetype
Printable View
1.yes it can be reprocessed multiple times but rarely happens
2.yes it's date datetype
Assuming you have no more than one reprocess per orderid/orderstageid query below should work. If you can have more than one reprocess per orderid/orderstageid additional logic is needed to sort out different reprocess items.
Hope this helps.Code:select a.orderid,
a.orderstageid,
a.processdate,
a.orderinfo
from order_history a
where a.orderid || a.orderstageid = (select b.orderid || b.orderstageid
from order_history b
where a.orderid || a.orderstageid = b.orderid || b.orderstageid
and b.orderinfo = 'reprocessed')
and a.orderinfo <> 'reprocessed'
and a.processdate >= (select c.processdate
from order_history c
where a.orderid || a.orderstageid = c.orderid || c.orderstageid
and c.orderinfo = 'reprocessed')
;