Jump to content

Time Metrics/Order Detail -MSSQL


tonysa
 Share

Recommended Posts

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

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

 

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...