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


Show source
| date   | sorting   | php   | sql   2017-01-04 20:01 1 Answers

Answers ( 1 )

  1. 2017-01-04 20:01

    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
    
◀ Go back