I have a MySQL db from which I would like to create a query where I can try and identify when date discrepancy occur. I'm not very used to refined SQL queries as I've been using mostly R and generally just refine everything there. I did my best to create a reprex of what I'm trying to achieve. Not that familiar with SQL, please be patient...
Unfortunately I cannot do this for this case (use R) and I'm trying to generate a summary where I'm able to identify invoice numbers, inv_num and dates where a difference occurred.
For example, BLW12345 where etd_origin_date - atd_origin_date = 0 (etd = estimated time departed, atd = actual time departed) and all other date differences for that inv_num are also 0.
BLW678910 on the other hand when evaluating etd_origin_date - atd_origin_date = -1, so on for the other fields being evaluated, which for this inv_num should all be zero.
Below is a query example that gives the "desired" layout or main idea, but the values are not what I'm expecting, the calculations did not work. In R everything is vectorized, so you can perform calculations on entire columns and have them as output, hence my approach, which hopefully is able to translate what I'm trying to do.
-- DESIRED QUERY (using "R mindset" which is vectorized). I suspect
-- some sort of looping with a variable is required to get this working properly:
SELECT etd_origin_date AS date , (
COUNT( etd_origin_date ) - COUNT( atd_origin_date )
) AS delta_origin_date , (
COUNT( eta_deprt_date ) - COUNT( ata_deprt_date )
) AS delta_deprt_date , (
COUNT( eta_dest_date ) - COUNT( ata_dest_date )
) AS delta_dest_date , (
COUNT( empty_pick_date ) - COUNT( empty_ret_date )
) AS delta_empty_date
FROM summary_repex
WHERE etd_origin_date <> atd_origin_date
GROUP BY date
I'm open to other approaches on how to handle similar the above concept. My attempt was based on my very rusty SQL and my R "mindset".
Please find below the full reprex:
CREATE TABLE summary_reprex(
inv_num CHAR(12),
etd_origin_date DATE,
atd_origin_date DATE,
eta_deprt_date DATE,
ata_deprt_date DATE,
eta_dest_date DATE,
ata_dest_date DATE,
empty_pick_date DATE,
empty_ret_date DATE);
INSERT INTO summary_reprex(inv_num, etd_origin_date, atd_origin_date,
eta_deprt_date, ata_deprt_date,
eta_dest_date,ata_dest_date,
empty_pick_date,empty_ret_date
)
VALUES
('BLW12345','2021-05-21', '2021-05-21', '2021-05-22','2021-05-22','2021-05-23', '2021-05-23', '2021-05-25','2021-05-25'),
('BLW678910','2021-05-21', '2021-05-22', '2021-05-22','2021-05-22','2021-05-23', '2021-05-23', '2021-05-25','2021-05-25'),
('BLW109876','2021-05-21', '2021-05-22', '2021-05-23','2021-05-23','2021-05-24', '2021-05-24', '2021-05-26','2021-05-26'),
('BLW54321','2021-05-21', '2021-05-21', '2021-05-23','2021-05-23','2021-05-24', '2021-05-24', '2021-05-26','2021-05-26'),
('BLW11223','2021-05-24', '2021-05-25', '2021-05-25','2021-05-26','2021-05-28', '2021-05-28', '2021-05-30','2021-05-31');
-- DESIRED QUERY (using "R mindset" which is vectorized). I suspect
-- some sort of looping with a variable is required to get this working properly:
SELECT etd_origin_date AS date,
inv_num, (
COUNT( etd_origin_date ) - COUNT( atd_origin_date )
) AS delta_origin_date , (
COUNT( eta_deprt_date ) - COUNT( ata_deprt_date )
) AS delta_deprt_date , (
COUNT( eta_dest_date ) - COUNT( ata_dest_date )
) AS delta_dest_date , (
COUNT( empty_pick_date ) - COUNT( empty_ret_date )
) AS delta_empty_date
FROM summary_repex
WHERE etd_origin_date <> atd_origin_date
GROUP BY date, inv_num
My intent for the query, is that it should only return dates and invoice_num where discrepancies occurred and a way to "mark" where it happened, on which delta_ ... so further investigation can be done.
I am very thankful for your time and patience.