CODEWITHSUNDEEP
mysql
CodingHero
CodingHero

Reputation: 683

MYSQL - Compare between 5 dates and order by most recent

I'm really blocked at an advanced query, if someone can help me

I have a table mysql that looks like:

customers(id, appointment_date1, appointment_date2, appointment_date3, appointment_date4)

I'm looking for a query that list me what is the next most recent appointment

Before I do this query :

SELECT CASE 
    WHEN (customers.appointment_date1 != '0000-00-00' AND DATE(customers.appointment_date1) >= CURDATE()) THEN customers.appointment_date1
    WHEN (customers.appointment_date2 != '0000-00-00' AND DATE(customers.appointment_date2) >= CURDATE()) THEN customers.appointment_date2
    WHEN (customers.appointment_date3 != '0000-00-00' AND DATE(customers.appointment_date3) >= CURDATE()) THEN customers.appointment_date3
    WHEN (customers.appointment_date4 != '0000-00-00' AND DATE(customers.appointment_date4) >= CURDATE()) THEN customers.appointment_date4
END as appointment
ORDER BY appointment ASC

But it's wrong, it doesn't work correctly.

Anyone can help?

Thanks

Upvotes: 0

Views: 77

Answers (1)

St3an
St3an

Reputation: 806

I'd use nested mysql if() functions in select clause, like :

select *
from(
    select if(date1<date2&&date1>curdate(),date1,
            if(date2<date3&&date2>curdate(),date2,
             if(date3>curdate(),date3, 'nothing')
            )
           ) as date
    from dates
) as dates
order by dates.date desc;

EDIT : as per Zika's comment

SELECT IF(LEAST(
           IFNULL(date1,'0000-00-00'),
           IFNULL(date2,'0000-00-00'),
           IFNULL(date3,'0000-00-00')
           )!='0000-00-00',
          LEAST(
           IFNULL(date1,'0000-00-00'),
           IFNULL(date2,'0000-00-00'),
           IFNULL(date3,'0000-00-00')
          ),
          'aucune date'
         )
FROM dates;

Upvotes: 1

Related Questions