SQL: How to select date data from two columns and order it using both columns

Question

I'm building a PHP/MySQL query that lists all reservations from database within a specified period (e.g. one day). All the reservations have start date and end date. I need to have them listed so that it shows them 1) in ascending order where start_date/end_date = between specified period 2) start_date & end_date can be within the period so it should have 1 result for the start_date and another for the end_date

SELECT *  
FROM `reservations` 
WHERE `start_date` BETWEEN '2017-01-04 00:00:00' AND '2017-01-04 23:59:59' 
   OR `end_date` BETWEEN '2017-01-04 00:00:00' AND '2017-01-04 23:59:00' 
order by start_date, end_date

So I need help ordering the results based on two columns AND having a duplicate result for a row that has both start_date and end_date between the specified period.

UPDATE

Now I have gotten so far that I have the correct results and the rows are ordered ascending based on the date (start_date/end_date). BUT if I have someone checking in-out during the same day (i.e. duplicate row), my current query lists the rows back-to-back, eventhough I would like the second row to be ordered based on the check-out time.

Here's what I have so far:

select * from ( SELECT id, start_date, end_date FROM reservations WHERE start_date BETWEEN '2017-01-03 00:00:00' AND '2017-01-03 23:59:59' OR end_date BETWEEN '2017-01-03 00:00:00' AND '2017-01-03 23:59:00' union all SELECT id, start_date, end_date FROM reservations WHERE start_date BETWEEN '2017-01-03 00:00:00' AND '2017-01-03 23:59:59' AND end_date BETWEEN '2017-01-03 00:00:00' AND '2017-01-03 23:59:00' ) as tbl order by case WHEN start_date BETWEEN '2017-01-03 00:00:00' AND '2017-01-03 23:59:59' THEN start_date ELSE end_date END ASC

Your query currently orders by start_date in ascending order and then by end_date (if two starts are equal). Is this not what you want? — noahnu
I think he wants the rows, in which start and end date apply on the condition, two times in the result. — Andy
ORDER BY is not working currently as I want it to work. I want it to "combine" both columns (start_date, end_date) and have them in ascending order. The goal is to print a list of checking-in/out customers in the right order by date and time. — Jukka Lepikkö

Anand Anand · Accepted Answer · 2017-01-04T19:13:27

You need a UNION ALL to duplicate the rows, since the OR condition will include them once, you can add an AND condition to duplicate only the ones that fall fully into that date range as follows:

select * from (
    SELECT *  
    FROM `reservations` 
    WHERE `start_date` BETWEEN '2017-01-04 00:00:00' AND '2017-01-04 23:59:59' 
       OR `end_date` BETWEEN '2017-01-04 00:00:00' AND '2017-01-04 23:59:00' 
    union all
    SELECT *  
    FROM `reservations` 
    WHERE `start_date` BETWEEN '2017-01-04 00:00:00' AND '2017-01-04 23:59:59' 
      AND `end_date` BETWEEN '2017-01-04 00:00:00' AND '2017-01-04 23:59:00' 
    ) as tbl
order by start_date, end_date

SQL: How to select date data from two columns and order it using both columns

1 Answers