Difference between two dates based on ID and condition

Question

I have to calculate the date difference between two rows for each unique ID (shipment-number) where the first time is categorized as (pickup) and the last time is categorized as (delivery). In essence, is there a way to create a calculated field or equivalent in Quicksight (or SQL) which does the following (where shipment number, event-type, and event-time are column names):

for each unique shipment-number find the date difference (event-time) between event-type == delivered and event-type == pickup

Assuming there are four rows for a specific shipment number:

shipment-number	event-type	event-type
001	pickup	11.01.2021
001	in-transit	12.01.2021
001	arrived-destination	13.01.2021
001	delivered	15.01.2021

then the expected result would be the difference from 11.01.2021 (pickup) and 15.01.2021 (delivered) which is 4 days.

I have tried using datediff, however, this requires two date fields. Creating two fields (firstEvent) and (lastEvent) by extracting the time of the event by the event-type for each unique shipment-number could be a possibility? However, I am unsure as to do so.

Any help/advice would be of great help.

Answer 1

You can use conditional aggregation as follows:

select shipment_number,
       datediff(day, min(case when event_type = 'pickup' then event_date end) 
       - max(case when event_type = 'delivered' then event_date end)  ) as diff
  from your_table
group by shipment_number