Dear all,
here with attached server table in the below.
let take increment_id
1 . if 100173953 status Pending to Processing it should not be moved more than 10 minutes
2. if 100173953 status Processing to Out delivery it should not be moved more than 15 minutes
3. and 100173953 status more than 120 minutes
above conditions true means I need that increment_id's for the below table
Someone, please help me
my code not working
- SELECT f.increment_id,
- ,TIMESTAMPDIFF(MINUTE,f.created_at,a.created_at) PP
- ,TIMESTAMPDIFF(MINUTE,a.created_at,b.created_at) PO
- ,TIMESTAMPDIFF(MINUTE,b.created_at,c.created_at) OC
- FROM sales_flat_order_status_history f
- INNER JOIN sales_flat_order_status_history a on a.parent_id = f.parent_id
- INNER JOIN sales_flat_order_status_history b on b.parent_id = f.parent_id
- INNER JOIN sales_flat_order_status_history c on c.parent_id = f.parent_id
- WHERE f.status ='pending'
- and a.status= 'processing'
- and b.status='out_delivery'
- and c.status ='complete'
- and
- (TIMESTAMPDIFF(MINUTE,f.created_at,CURRENT_TIMESTAMP()) >10 or
- TIMESTAMPDIFF(MINUTE,a.created_at,CURRENT_TIMESTAMP()) >15 or
- TIMESTAMPDIFF(MINUTE,b.created_at,CURRENT_TIMESTAMP()) >30
- )and f.created_at BETWEEN NOW() - INTERVAL 60 DAY AND NOW()
- GROUP BY f.increment_id order by f.increment_id ;