tonysa Posted March 18, 2021 Share Posted March 18, 2021 I have the following code that i need to alter to add additional time metrics such as: Document Number, 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? 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' Order Detail Template - for BI.xlsx Link to comment Share on other sites More sharing options...
tonysa Posted March 19, 2021 Author Share Posted March 19, 2021 Quote 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; Link to comment Share on other sites More sharing options...
tonysa Posted March 19, 2021 Author Share Posted March 19, 2021 Quote 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; Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now