MySQL Forums
Forum List  »  General

If and Time Statements
Posted by: Anthony Apollis
Date: March 13, 2021 04:51PM

Scenario: Groceries ordering app. The following code brings in the time when a customer places an order till he/she receives the order.

SELECT DATE(o.created_datetime) as Date, o.store_uid as 'Store UID'
, o.uid as 'Order'
, o.rating as 'Rating'
, IF(o.accepted_datetime < o.driver_ready_datetime, TRUE, FALSE) as 'Switch'
, timestampdiff(minute, o.created_datetime, o.accepted_datetime) as 'Accepted Order'
, if(accepted_datetime < driver_ready_datetime, timestampdiff(minute, o.accepted_datetime, o.driver_ready_datetime), timestampdiff(minute, o.created_datetime, o.driver_ready_datetime)) as'Invoiced Order'
, timestampdiff(minute, o.created_datetime, o.delivered_datetime) as 'Delivery Time'
, ROUND(if(o.amended_items_total IS NOT NULL, o.amended_items_total, o.items_total)
+ if(o.amended_bottles_fee IS NOT NULL, o.amended_bottles_fee, o.bottles_fee)
+ if(o.amended_delivery_fee IS NOT NULL, o.amended_delivery_fee, o.delivery_fee),2) as 'Turnover'
FROM `order` o
WHERE o.status = 'D' AND NOT store_uid IN (6,56, 202)
AND date(o.created_datetime) >= '2020-11-01'

I would like to bring in: Order Status, Time Elapsed Minutes, Created Date, Created Time, Accepted By Store, Minutes To Accepted By Store, Invoiced At, Mins Elapsed Invoiced, Driver Accepted, Mins Driver Accepted, Driver At Store, Mins Driving To Store, Mins Driver In Store, Driver Starts Delivery, Driver Arrival At Client, Mins Driving To Client Driver Completes Delivery, Total Mins Elapsed To Delivery


Please assist?

Options: ReplyQuote


Subject
Written By
Posted
If and Time Statements
March 13, 2021 04:51PM
March 13, 2021 07:34PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.