Add a new column and replace NULL values

Question

DB-Fiddle

CREATE TABLE PDW_FCT_Logistics (

    id INT primary key,
    cID VARCHAR(255),
    event_type VARCHAR (255),
    delivery_estimate DATE,
    delivered_time DATE,
    complete_time DATE,
    plan_offer_quantity INT,
    order_quantity INT, 
    received INT
);

INSERT INTO PDW_FCT_Logistics
(id, cID, event_type,
delivery_estimate, delivered_time, complete_time,
plan_offer_quantity, order_quantity, received
)
VALUES 
("1", "46902", "offer", "2020-02-03", NULL, NULL, "6145", NULL, NULL),
("2", "46902", "order", "2020-02-03", NULL, NULL, NULL, "2145", NULL),
("3", "46902", "stock_in", "2020-02-03", "2020-02-10", NULL, NULL, NULL, NULL),

("4", "55519", "offer", "2020-05-09", NULL, NULL, "4337", NULL, NULL),
("5", "55519", "order", "2020-05-09", NULL, NULL, NULL, "4018", NULL),
("6", "55519", "stock_in", "2020-05-09", "2020-05-12", "2020-07-08", NULL, NULL, "3989");

I want to change the structure of the table above to the following:

id      cID        event_type     sub_event_type      event_date           quantity
1       46902      offer          NULL                2020-02-03            6145
2       46902      order          NULL                2020-02-03            2145
3       46902      stock_in       delivered           2020-02-10            2145
4       55519      offer          NULL                2020-05-09            4337
5       55519      order          NULL                2020-05-09            4018
6       55519      stock_in       delivered           2020-05-12            4018
6       55519      stock_in       completed           2020-07-08            3989

The main difference to the orignal data is that the the date and quanttiy columns are switched to rows and that a sub_event_type is added as column. The sub_event_type is added once a cID does have the event_type stock_in and

delivered_time IS NOT NULL = delivered
complete_time IS NOT NULL = completed

---

In order to achieve this I went with the solution from this question:

 SELECT
  id,
  cID,
  event_type, 
  'delivered' AS sub_event_type, 
  delivered_time AS event_date,
  order_quantity AS quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'stock_in' AND delivered_time IS NOT NULL

UNION ALL

  SELECT
  id,
  cID,
  event_type,
  'completed' AS sub_event_type,
  complete_time AS event_date,
  received as quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'stock_in' AND complete_time IS NOT NULL

UNION ALL

  SELECT
  id,
  cID,
  event_type, 
  NULL AS sub_event_type,
  delivery_estimate AS event_date,
  plan_offer_quantity AS quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'offer' 

UNION ALL

  SELECT
  id,
  cID,
  event_type, NULL AS sub_event_type,
  delivery_estimate AS event_date,
  order_quantity AS quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'order'

ORDER BY 1;

It almost gives me the result I need. However, the issue ist that for cID 46902 there is no quantity in the event_type stock_in. In this case I want that the quantity of the event_type order is used for the sub_event_type delivered.

What do I need to change in my query to make this work?

Answer 1

One option would be to use a correlated sub-select in the delivery portion of your query. In this case, it re-queries the original data to find the quantity associated with the order event of the same cID.

 SELECT
  id,
  cID,
  event_type, 
  'delivered' AS sub_event_type, 
  delivered_time AS event_date,
  CASE WHEN order_quantity IS NOT NULL THEN order_quantity --<--Edit starts here
       ELSE (SELECT order_quantity 
             FROM PDW_FCT_Logistics AS ss
             WHERE ss.cID = PDW_FCT_Logistics.cID
             AND ss.event_type = 'order')
  END AS quantity                                          --<--and ends here.
  FROM PDW_FCT_Logistics
  WHERE event_type = 'stock_in' AND delivered_time IS NOT NULL

UNION ALL

  SELECT
  id,
  cID,
  event_type,
  'completed' AS sub_event_type,
  complete_time AS event_date,
  received as quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'stock_in' AND complete_time IS NOT NULL

UNION ALL

  SELECT
  id,
  cID,
  event_type, 
  NULL AS sub_event_type,
  delivery_estimate AS event_date,
  plan_offer_quantity AS quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'offer' 

UNION ALL

  SELECT
  id,
  cID,
  event_type, NULL AS sub_event_type,
  delivery_estimate AS event_date,
  order_quantity AS quantity
  FROM PDW_FCT_Logistics
  WHERE event_type = 'order'

ORDER BY id;

I also changed the ORDER BY clause. Using column ordinals is OK for ad hoc queries, but should be avoided in production code. See Bad habits to kick : ORDER BY ordinal for more detail.

Here's the code at work in your DBFiddle. (And thank you for setting that up!