How to calculate difference in dates for column with smallest value

user_17346778 0
Viewed 54

I have a table that contains 3 column dates: CREATED, CLOSED and EXPIRED. I need to calculate the difference in date between either CLOSED or EXPIRED and CREATED. I need to select the SMALLER value between CLOSED and EXPIRED and then calculate the difference to CREATED. If they have the same date, I need to select EXPIRED.

enter image description here

In the example above:

For account_id =1, the difference in date should be: EXPIRED - CREATED. 
For account_id = 2, the difference in date should be EXPIRED - CREATED.
For account_id = 3, the difference in date should be CLOSED - EXPIRED.

Is there a way to do this, to select the smaller value between CLOSED and EXPIRED, and based on this calculate the difference in date with CREATED?

snowflake-schema snowflake-cloud-data-platform sql
1 Answers

LEAST or GREATEST is how you pick the min/max between values.

SELECT 
   created, expired, closed,
   LEAST(expired, closed) as min_exp_clo,
   DATEDIFF('days', created, min_exp_clo) as date_diff_days
FROM table

and have a extra column free version, just mash together:

SELECT 
   created, expired, closed,
   DATEDIFF('days', created, LEAST(expired, closed)) as date_diff_days
FROM table
user_439920
Related