MYSQL - Compare between 5 dates and order by most recent

Question

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

Do you need THE next most recent appointment or the list of the next most recent appointments ? — Loufylouf, Mar 20 '16 at 21:29
You're having to work harder by not normalizing this structure. Try using a new table `appointments` rather than using a bunch of columns in the customers table. — Devon Bessemer, Mar 20 '16 at 21:30
What do you mean by 'doesn't work correctly'? Could you provide sample data, expected result and current result? — piotrgajow, Mar 20 '16 at 21:43
This will help with sorting by date: http://stackoverflow.com/questions/9511882/sorting-by-date-time-in-descending-order but you may want to put the data into a temporary table first, so you can get the data. http://stackoverflow.com/questions/5859391/create-a-temporary-table-in-a-select-statement-without-a-separate-create-table — James Black, Mar 20 '16 at 21:45

St3an · Answer 1 · 2016-03-21T08:01:12.947

1

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;

edited Mar 21 '16 at 08:01

answered Mar 20 '16 at 21:50

St3an

726
1
6
21

And why is that? I think that CASE is more readable in this example – piotrgajow Mar 20 '16 at 22:16
1

using `CASE` here assumes that appointment_date1 – St3an Mar 20 '16 at 22:31
1

I guess you can use `LEAST()` function (http://stackoverflow.com/questions/2615697/mysql-biggest-value-from-two-or-more-fields) actually :) – St3an Mar 20 '16 at 23:39

MYSQL - Compare between 5 dates and order by most recent

1 Answers1