CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformsnowflake-schema
lalaland
lalaland

Reputation: 341

How to calculate difference in dates for column with smallest value

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?

Upvotes: 2

Views: 150

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

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

Upvotes: 3

Related Questions