0

I have a table te_dates that holds order status information for orders.

The table is a many-to-one relationship, so one order can have many row inserts into my order_status table.

(dsc_id is the order status code)

order_id | dsc_id |             te_date | te_date_id
----------------------------------------------------
    2880 |      1 | 2014-11-19 09:27:18 |      16486
    2880 |      2 | 2014-11-19 09:27:23 |      16487
    2880 |      3 | 2014-11-24 14:10:40 |      16710
    2880 |      4 | 2014-11-24 14:10:41 |      16711
    2880 |      0 | 2014-11-24 14:10:49 |      16712
    2880 |      8 | 2014-12-17 15:52:29 |      17782

My client is asking me to find all orders that are 60 days or older that have NOT been closed, and change their status.

My thought process is to select distinct order_id of files that have been opened (dsc_id = 1) that are greater than 60 days old, and are not currently closed (dsc_id = 8).

I just can't wrap my head around the query.

Any help would be appreciated.

0
SELECT
    o1.order_id
FROM tblOrders o1
WHERE 
    o1.dsc_id = 1
    AND o1.te_date < DATE_ADD(NOW(), INTERVAL -60 DAY)
    AND NOT EXISTS (SELECT order_id FROM tblOrders o2 WHERE o2.order_id = o1.order_id and o2.dsc_id = 8)
    AND EXISTS (SELECT order_id FROM tblOrders o2 WHERE o2.order_id = o1.order_id and o2.dsc_id != 1)
GROUP BY o1.order_id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.