Skip to content
Advertisement

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?

Answer

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