Add new column to result and coalesce table

DB Fiddle

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

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

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

I want to run a query that:

a) adds a new column sub_event_type to the above table and
b) coalesce the table so all date values are in one column called event_date

The result should look like this:

campaign     event_type      sub_event_type     event_date         quantity
C001         offer           NULL               2019-04-10          500
C001         order           NULL               2019-04-16          450
C001         stored          delivered          2019-04-18          465
C001         stored          recorded           2019-05-20          465
C002         offer           NULL               2019-08-14          700
C002         order           NULL               2019-09-04          730
C002         stored          completed          2019-09-15          800
C002         stored          delivered          2019-09-18          800
C002         stored          recorded           2019-09-19          800

In order to achieve this I tried to go with this query:

SELECT
id,
campaign,
event_type,

 (CASE
  WHEN event_type = "stored" AND date_delivered IS NOT NULL THEN "delivered"
  WHEN event_type = "stored" AND date_recorded IS NOT NULL THEN "recorded"
  WHEN event_type = "stored" AND date_completed IS NOT NULL THEN "completed"
  END) AS sub_event_type,

coalesce(date_offered, date_ordered, date_delivered, date_recorded, date_completed) as event_date,
quantity
FROM logistics;

However, this query only displays sub_event_type delivered in the result.
I assume the reason for this issue ist that in the event_type stored all date values are in one row and in the event_types offered and stored they are in two seperate rows.

How do I have to modify my query to get the expected result?

Answer

I think you need union all:

select id, campaign, event_type, 'delivered' as sub_event_type, date_delivered
from logistics
where event_type = 'stored' and date_delivered is not null
union all
select id, campaign, event_type, 'recorded' as sub_event_type, date_recorded
from logistics
where event_type = 'stored' and date_recorded is not null
union all
select id, campaign, event_type, 'completed' as sub_event_type, date_completed
from logistics
where event_type = 'stored' and date_completed is not null
union all
select id, campaign, event_type, null as sub_event_type, date_offered
from logistics
where event_type = 'offered' 
union all
select id, campaign, event_type, null as sub_event_type, date_ordered
from logistics
where event_type = 'ordered' ;

Advertisement

Answer