Coalesce columns and replace NULL value

Question

DB-Fiddle

CREATE TABLE logistics (
    id int primary key,
    campaign VARCHAR(255),
    event_type VARCHAR (255),
    date_offered DATE,
    date_ordered DATE, 
    date_delivered DATE,
    quantity VARCHAR(255)
);

INSERT INTO logistics
(id, campaign, event_type, 
date_offered, date_ordered, date_delivered, quantity
)
VALUES 
("1", "C001", "offered", "2019-04-10", NULL, NULL, "500"),
("2", "C001", "ordered", NULL, "2019-04-16", NULL, "450"),
("3", "C001", "stored", NULL, NULL, "2019-04-18", NULL),

("4", "C002", "offered", "2019-08-14", NULL, NULL, "700"),
("5", "C002", "ordered", NULL, "2019-09-04", NULL, "730"),
("6", "C002", "stored", NULL, NULL, "2019-09-15", "800");

I want to run a query that coalesce the values so:

a) all date values are in one column called event_date and
b) in case there is no quantity for the event_type stored (as you can see for C001) the quantity of the event_type order should be used.

The result should look like this:

campaign     event_type       event_date         quantity
C001         offered          2019-04-10          500
C001         ordered          2019-04-16          450
C001         stored           2019-04-18          450
C002         offered          2019-08-14          700
C002         ordered          2019-09-04          730
C002         stored           2019-09-15          800

I tried to go with this:

SELECT id,
       campaign,
       event_type,
       coalesce(date_offered, date_ordered, date_delivered) as event_date,
       quantity
  FROM logistics;

With this query I get close to my expected results but for C001 there is a NULL for C001 in the event_type stored.

How do I have to modify my query to get the quantity of the event_type ordered for the event_type stored for C001?

Answer 1

You can use correlated sub-query :

SELECT l.id, l.campaign, l.event_type,
       coalesce(l.date_offered, l.date_ordered, l.date_delivered) as event_date,
       coalesce(l.quantity,
                      (SELECT MAX(l1.quantity)
                       FROM logistics l1
                       WHERE l1.event_type = 'ordered' AND l.campaign = l1.campaign
                      )
               ) as new_quantity
FROM logistics l;