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.
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?
Advertisement
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