I have an order table in Postgres with order number and status as column. If a new order is placed the default status were Order placed and Goods Assigned.
order 12345
date order status
2020-06-01 12345 Goods Assigned
2020-06-01 12345 Order placed
order 543223
date order status
2020-05-02 54321 Reached pickup hub
2020-05-01 54321 Goods Assigned
2020-05-01 54321 Order placed
I want to know if the order were out from the sender place. So I want to create a column called started and has value Yes if we have any status after ‘Order placed’ and ‘Goods Assigned’. Else No if have only those two condition as below in Select statement
order Started
12345 No
54321 Yes
As ‘Order placed’ and ‘Goods Assigned’ was available in all the order I am unable to use NOT IN operator. Is there any other way we can get a report.
Advertisement
Answer
You can use aggregation:
select order_id, bool_or(status not in ('Goods Assigned', 'Order placed')) is_started
from mytable
group by order_id
For each order_id
, this produces a boolean flag that indicates whether any ot its statuses has a value other than 'Goods Assigned'
and 'Order placed'
.