March 18, 2021 at 8:07 am
I have the following code(mssql) 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'
March 18, 2021 at 6:15 pm
If you provide some DDL and sample data, I'm sure someone can help. With what you provided (excel not tested as that is always risky), we have no way to know what your table(s) contain. Does the order table have the document number (for example)?
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 18, 2021 at 8:03 pm
Posting consumable DDL & data serves several purposes:
March 18, 2021 at 8:10 pm
It is as ratbak says, excel files don't help us. We have no easy way to take an Excel file and create temp tables from it. Plus, the excel file lacks any table details. We have no idea on the datatypes (just guesses from Excel). For example, if you want to get the Created Date and Created Time, I would assume those columns would be a single DATETIME column, but maybe you have it stored as a string and we need to convert it first.
Providing DDL allows us to ensure datatypes and required objects (indexes, keys, triggers (if any), etc) and relationships between tables are accurate.
On top of that, IT security best practices state that you should never trust a file you downloaded from an unknown source. As far as I know, you have a macro in there to toss some ransomware on my machine and are just hoping I'm willing to risk it.
Lastly, by providing DDL and sample input as well as expected output, it helps us help you. We can run the code and get your current result set and compare it to the expected output and help determine where the problem is. We can then lead you to the solution.
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 18, 2021 at 9:31 pm
First, I could be mistaken, but that does not look like SQL Server code. Is that MySQL or PostgreSQL?
Secondly, that screenshot is not "consumable" by us.
Third, the screenshot only shows part of the picture. It is showing part of the table structure but is missing any sample data and sample output.
I am not trying to be difficult, but so far you haven't really given us anything we can work with. What we need is some TSQL code that we can run. Something like:
CREATE TABLE #tmpTable (ID INT, Data VARCHAR(MAX));
INSERT INTO #tmpTable
VALUES (1,'hello'),
(2,'world');
--Expected output:
-- OUTPUT
-- hello world
SELECT 'hello world' AS OUTPUT
The above code, we can see what your expected output is and anyone can grab the code and run with it and test it. And for an example solution, someone may say:
SELECT STRING_AGG(Data,' ') AS OUTPUT
FROM #tmpTable
it gives us something to work with. You have not provided us with anything useable in SQL Server, but instead have left us with Excel (not directly transferrable over to SQL) and a PNG (also not directly transferrable over to SQL), so it is challenging for anyone to help you. Help us help you!
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 18, 2021 at 9:58 pm
A picture is worse than an Excel spreadsheet - we can't take that information and create a test. You also didn't provide sample data...
BTW - this forum deals with Microsoft SQL Server and you are using a different platform. I am not even sure what database system you are using or even the tool shown in your picture.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 19, 2021 at 6:04 pm
Here it is:
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;
March 19, 2021 at 6:57 pm
So that is progress, but still 2 issues. First - there is no sample data. Second - that is not Microsoft SQL Server code.
But, from looking at what you posted, getting the document number is not going to be possible as it doesn't appear to be a column in your table. So, right away, that field is not possible to obtain with the data we have been given.
but lets break down some of your new column requirements:
Document Number <-- does not exist, so no solution
Status <-- column exists, so just add it in to the select list
Time Elapsed Minutes <-- need to define what time is being elapsed. You have a lot of date related columns
Created Date <-- column exists, so just add it in to the select list
Created Time <-- column exists (created date), so use that
Accepted By Store <-- what is this supposed to be? a bit? a datetime? what column corresponds with this?
Minutes To Accepted By Store <-- what makes it accepted and minutes from which column to accepted?
Invoiced At <-- column appears to exist
Mins Elapsed <-- elapsed from what?
Invoiced <-- is this the invoice status column?
etc.
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 19, 2021 at 9:15 pm
Yes, i know this is for mssql only. Any idea what the simialar code or equavalent for mysql is? data attached
March 19, 2021 at 9:18 pm
Here is the data: https://drive.google.com/file/d/1LoaPrLoz_nlAD1wnrkNaEFQDYkVmKweB/view?usp=sharing
Yes, i know this is for mssql only. Any idea what the mysql equavalent look like?
March 19, 2021 at 9:35 pm
I do not know of any forum similar to this for MySQL. I do not use MySQL myself.
But I think your query for the most part should translate between the different tools pretty easily. I expect MySQL has some sort of DATEDIFF function you could use to get your elapsed times. Just get a datediff likely in minutes, maybe seconds or hours or days depending on your requirements, between the 2 dates you want to know elapsed.
Also, the forum has a section dedicated to MySQL - https://www.sqlservercentral.com/forums/forum/sql-server-and-other-platforms/mysql
Posting it under SQL Server 2012 - General is going to get responses related to SQL Server 2012.
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply