Jump to content

Time Metrics/Order Detail -MSSQL

tonysa

By tonysa
in SQL

 Share

Recommended Posts

tonysa Newbie

tonysa

Posted
Posted

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
tonysa Newbie

tonysa

Posted
  • Author
Posted
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
tonysa Newbie

tonysa

Posted
  • Author
Posted

 

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
Go to topic listing
×
×
  • Create New...