March 22, 2021 at 9:02 pm
I have the following code for time and dates metrics:
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 more metrics, such as status, time laps ect. See attached(Order Detail).
See DDL below:
CREATE TABLE order (
uid int(10) unsigned NOT NULL AUTO_INCREMENT,
delivery_address varchar(256) NOT NULL,
complex_unit_no varchar(200) DEFAULT NULL,
delivery_latitude varchar(20) NOT NULL,
delivery_longitude varchar(20) NOT NULL,
store_uid int(10) unsigned NOT NULL,
app_version varchar(10) DEFAULT NULL,
delivery_fee varchar(10) NOT NULL,
amended_delivery_fee varchar(10) DEFAULT NULL,
bottles_fee varchar(10) NOT NULL,
amended_bottles_fee varchar(10) DEFAULT NULL,
promo_code varchar(50) NOT NULL,
promo_discount varchar(10) NOT NULL,
promo_discount_amount varchar(10) NOT NULL,
promo_discount_type varchar(10) NOT NULL,
user_agent_string varchar(512) DEFAULT NULL,
amended_promo_code varchar(50) DEFAULT NULL,
amended_promo_discount varchar(10) DEFAULT NULL,
amended_promo_discount_amount varchar(10) DEFAULT NULL,
amended_promo_discount_type varchar(10) DEFAULT NULL,
delivery_notes longtext NOT NULL,
items_total varchar(30) NOT NULL,
amended_items_total varchar(10) DEFAULT NULL,
order_total varchar(10) NOT NULL,
this_order_total varchar(10) DEFAULT NULL,
amended_order_total varchar(30) DEFAULT NULL,
adjusted_items_total varchar(30) DEFAULT NULL,
adjusted_datetime datetime DEFAULT NULL,
adjusted_order_total varchar(10) DEFAULT NULL,
outstanding_amount varchar(10) DEFAULT NULL,
crc varchar(10) NOT NULL,
created_datetime datetime NOT NULL,
placed_datetime datetime DEFAULT NULL,
transaction_sequence varchar(20) NOT NULL,
user_uid int(11) NOT NULL,
status varchar(3) DEFAULT NULL,
accepted_datetime datetime DEFAULT NULL,
picking_datetime datetime DEFAULT NULL,
edi_order_no varchar(50) DEFAULT NULL,
edi_invoice_status enum('NONE','QUEUED','PENDING','ERROR','SUCCESS','SENT') DEFAULT 'NONE',
edi_invoice_no varchar(50) DEFAULT NULL,
edi_message_response varchar(500) DEFAULT NULL,
last_status_changed_datetime datetime DEFAULT NULL,
edi_credit_status enum('NONE','QUEUED','PENDING','ERROR','SUCCESS','SENT') NOT NULL DEFAULT 'NONE',
amended_datetime datetime DEFAULT NULL,
vendor_amended_datetime datetime DEFAULT NULL,
vendor_viewed_datetime datetime DEFAULT NULL,
user_amending_datetime datetime DEFAULT NULL,
user_substituted enum('Y','N') DEFAULT 'N',
rating varchar(1) DEFAULT NULL,
rating_comment varchar(2000) DEFAULT NULL,
confirmed_datetime datetime DEFAULT NULL,
delivered_datetime datetime DEFAULT NULL,
delivery_integration_enabled enum('Y','N') DEFAULT 'N',
paid_in_full enum('Y','N') DEFAULT 'N',
driver_name varchar(80) DEFAULT NULL,
driver_contact_no varchar(40) DEFAULT NULL,
invoice_no varchar(20) DEFAULT NULL,
ppay_requested enum('Y','N') NOT NULL DEFAULT 'N',
ppay_payment_id varchar(50) DEFAULT NULL,
driver_cancelled enum('Y','N') NOT NULL DEFAULT 'N',
bottles_notes varchar(150) DEFAULT NULL,
bottles_agent_name varchar(50) DEFAULT NULL,
driver_started_delivery enum('Y','N') NOT NULL DEFAULT 'N',
driver_tracking_link varchar(300) DEFAULT NULL,
vendor_need_help enum('Y','N') DEFAULT 'N',
has_vendor_viewed_order enum('Y','N') DEFAULT 'N',
picking_slip_pn_sent enum('NONE','PICKING_SLIP','AMENDED_PICKING_SLIP') DEFAULT 'NONE',
vendor_ready_for_driver enum('Y','N') DEFAULT 'N',
driver_ready_datetime datetime DEFAULT NULL,
driver_status enum('CANCELLED','DECLINED','NONE','FAILED TO DELIVER') DEFAULT 'NONE',
vendor_driver_collected enum('Y','N') DEFAULT 'N',
delivery_integration_service_name enum('NONE','JARVIS','TOOKAN','PICUP') DEFAULT 'NONE',
show_driver_tracking enum('Y','N') DEFAULT 'N',
payment_method enum('3DS','DB','NONE') DEFAULT 'NONE',
possible_fraud enum('Y','N') DEFAULT 'N',
invoice_status enum('SENT','NOT_SENT') DEFAULT 'NOT_SENT',
driver_tip decimal(10,2) DEFAULT '0.00',
order_type enum('GV','OD','MO') DEFAULT 'OD',
picker_name varchar(100) DEFAULT NULL,
PRIMARY KEY (uid),
UNIQUE KEY transaction_sequence_user_uid (transaction_sequence,user_uid),
KEY store_uid (store_uid),
KEY user_uid (user_uid),
KEY idx_created_datetime (created_datetime),
KEY store_and_status (store_uid,status),
KEY status (status),
KEY store_user_status (store_uid,user_uid,status),
KEY promocode_status (promo_code,status),
KEY user_promcode_status (user_uid,promo_code,status),
KEY edi_order_no (edi_order_no),
KEY voucher_payment (order_type,status,edi_invoice_status)
) ENGINE=InnoDB AUTO_INCREMENT=846063 DEFAULT CHARSET=utf8;
What would the syntax look like, say i want to start with Status?
Thanks.
March 22, 2021 at 9:25 pm
I personally don't know MySQL, but I think it really depends. To change the order of the columns, you change the order of them in your SELECT. For example, if you want STATUS first, your query should start with "SELECT o.status," followed by whichever column should come next.
Now why I say "it depends" is that this should have MySQL present it back that way, but if your tool is designed to put "Order" first, it doesn't matter what you do on the database side. The application sees the column named "Order" and will put that one where it wants it.
Now, I'd also like to add that bringing in Status is a boring piece of data. Why is it boring? Because your WHERE clause is limiting the status to ONLY being 'D'. So, the only status you will have in your query is going to be the value 'D' UNLESS you remove that part of your where clause. Then it may be more interesting.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 22, 2021 at 9:33 pm
This is a post to the more appropriate forum of the question asked in Metrics/Order Detail -MSSQL, in which you were repeatedly, politely asked NOT to post excel file attachments or pictures, but to instead post SQL statements that create the tables and views involved, and populate sample data, per https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help.
Don't you want others to help you? If so, please provide the information needed, in consumable SQL format, for others to do so.
March 23, 2021 at 12:04 pm
Wouldn't you just add o.status to the SELECT list? I'm confused by what the question is.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply