How to create SQL table with columns from multiple tables

Question

I have three tables in MySQL 'Customers', 'Deliverys_History'. The 'Customers' table has the customer ID and name information, while the 'Deliverys_History' table has the relationship of the orders placed by a customer, the status change date and the status_id.

I need to create an auxiliary table (for later export) that has the customer id, name, and order arrival date vs order ship date to make a minute differentiation between them.

Customers table

CustomerID	CustomerName
000001	Pepe
000002	John
000003	Adi
000004	Louis

History table

Timestamp	StatusID	CustomerID	ArticleID
2022-12-15 16:40:00	11	000001	520369
2022-12-15 18:42:24	11	000002	520370
2022-12-15 20:44:48	11	000003	520371
2022-12-15 22:47:12	12	000001	520372
2022-12-16 00:49:36	11	000003	520373
2022-12-16 02:52:00	11	000001	520374
2022-12-16 04:54:24	11	000004	520375
2022-12-16 06:56:48	12	000001	520376
2022-12-16 16:40:00	12	000002	520377
2022-12-16 18:42:24	12	000003	520378
2022-12-16 20:44:48	12	000003	520379
2022-12-16 22:47:12	12	000004	520380
2022-12-17 00:49:36	11	000001	520381
2022-12-17 02:52:00	12	000001	520382

Output

CustomerID	CustomerName	Lap	Arrive(11)	Dispatch(12)	Diff (min)
000001	Pepe	1	2022-12-15 16:40:00	2022-12-15 18:42:24	122
000001	Pepe	2	2022-12-16 02:52:00	2022-12-16 06:56:48	245
000001	Pepe	3	2022-12-17 00:49:36	2022-12-17 02:52:00	122
000002	John	1	2022-12-15 18:42:24	2022-12-16 16:40:00	1318
000003	Adi	1	2022-12-15 20:44:48	2022-12-16 18:42:24	1318
000003	Adi	2	2022-12-16 00:49:36	2022-12-16 20:44:48	1195

The lap column must be the key to extract de dates relationship.

Thank you!

Hi - How do we tell which records for the same customer refer to the same order? As this is not a free code writing service, please update your question with the SQL you’ve managed to write on your own and ask a specific question about an issue you are facing — NickW, Feb 22 '23 at 14:37
Your addition of `ArticleID` appears to be just a surrogate key. Where is the OrderID relationship? What relates a StatusID 11 row to a StatusID 12 row? — user1191247, Feb 22 '23 at 16:08

score 0 · Answer 1 · answered Feb 22 '23 at 16:58

You do not have the OrderID, so there is no reliable way of linking status changes for an order. The following uses an intermediate cte to apply a "made up" order number:

WITH Deliveries_History (Timestamp, StatusID, CustomerID, OrderID) AS (
    SELECT Timestamp, StatusID, CustomerID, ROW_NUMBER() OVER (PARTITION BY CustomerID, StatusID ORDER BY Timestamp)
    FROM Deliverys_History
)
SELECT
    a.CustomerID,
    c.CustomerName,
    ROW_NUMBER() OVER (PARTITION BY a.CustomerID ORDER BY a.Timestamp) AS Lap,
    a.Timestamp AS Arrive,
    d.Timestamp AS Dispatch,
    TIMESTAMPDIFF(MINUTE, a.Timestamp, d.Timestamp) AS Diff
FROM Deliveries_History a
JOIN Deliveries_History d
    ON a.CustomerID = d.CustomerID
    AND a.OrderID = d.OrderID
    AND d.StatusID = 12
JOIN Customers c
    ON a.CustomerID = c.CustomerID
WHERE a.StatusID = 11
ORDER BY a.CustomerID, a.Timestamp;

This should not be replied upon due to the "made up" OrderID!

How to create SQL table with columns from multiple tables

1 Answers1