SQL Query for Order Status report from Oracle DB

Question

I have a table Order_Status in Oracle DB 11, which stores order id and all its status for ex


ample

order id    status                  date
100         at warehouse            01/01/18
100         dispatched              02/01/18
100         shipped                 03/01/18
100         at customer doorstep    04/01/18
100         delivered               05/01/18

a few days back some of the orders were stuck in warehouse but it is not possible to check status of each order every day so no one noticed until we received a big escalation mail from business which arouse the requirement of a system or daily report which will tell us about status of all the order along with there present status and with some condition like if there are more than 2 days and no new status has been updated in DB for the order then mark it in red or highlight it.

we already have cron scheduled some of our reports but even if a create a SQL query for the status report it won't highlight pending order.

Note:- SQL, Java or some other tool suggestions both are welcome but SQL preferred then Tool then java.

Answer 1

I am assuming that your requirement is "status will always change in every 2 days, if not there is something wrong"

select * from (    
    select order_id, 
               status, 
               update_date, 
               RANK() OVER (PARTITION BY order_id ORDER BY update_date DESC) as rank 
        from Order_Status 
        where status != 'delivered'   
)
where update_date < sysdate - 2 and rank = 1