3

Background

I have a table Deliveries:

+---------+-------------+---------------+
| courier | pickup_date | delivery_date |
+---------+-------------+---------------+
| Sebby   | 2015-05-02  | 2015-05-04    |
| Ian     | 2015-05-07  | 2015-05-08    |
| Sebby   | 2015-05-12  | 2015-05-16    |
| Bart    | 2015-05-18  | 2015-05-21    |
| Ian     | 2015-05-27  | 2015-05-29    |
+---------+-------------+---------------+

Visually, The courier's schedule looks like this:

enter image description here

Question

The search criteria is a date range and I have to return which courier is 'available' on that date range. For example, the search criteria is from 2015-05-16 - 2015-05-17. Looking at the table or the calendar, it's easy to know that only Ian and Bart are available on those dates. How do I do this in MySQL?

What I have tried

I saw this answer so I tried it like so:

SELECT courier FROM Deliveries
WHERE pickup_date > '2015-05-17'
      OR delivery_date < '2015-05-16'

This gives me Ian and Bart. But it also includes Sebby. This is because although Sebby's 2015-05-12 - 2015-05-16 overlaps with the search criteria, 2015-05-02 - 2015-05-04 doesn't so it is returned in the query. I need to do something like restrict the query. If the courier's schedule overlaps once, then don't return it in the query. I could do this in the source code but I prefer doing all the filtering in MySQL. Source code will be the last resort.

This popped out on questions that may already have my answer and it seemed similar to my earlier attempt.

This question (and the answer) also talks about my earlier attempt. Everything else that I saw seems related one way or another but does not answer my question.

Am I on the right track? Or my approach to the problem is wrong?

Schema

I will provide the script to create the table to save you the time :)

CREATE TABLE Deliveries (
    courier varchar(15),
    pickup_date date,
    delivery_date date
);

INSERT INTO Deliveries (courier, pickup_date, delivery_date) VALUES
('Sebby', '2015-05-02', '2015-05-04'),
('Ian', '2015-05-07', '2015-05-08'),
('Sebby', '2015-05-12', '2015-05-16'),
('Bart', '2015-05-18', '2015-05-21'),
('Ian', '2015-05-27', '2015-05-29');
Community
  • 1
  • 1
Keale
  • 3,924
  • 3
  • 29
  • 46

4 Answers4

2

Something like this may work for you. I'd also suggest you have another table for just your courier names, to make things a little more optimal.

select distinct courier
  from deliveries d
    where not exists (
      select 1 from deliveries d2
        where d.courier = d2.courier
          and (('2015-05-16' between pickup_date and delivery_date)
                  or ('2015-05-17' between pickup_date and delivery_date)
                  or (pickup_date between '2015-05-16' and '2015-05-17')
                  or (delivery_date between '2015-05-16' and '2015-05-17'))
    )

demo here

Through the use of where not exists, this should exclude any courier who:

  1. has a delivery that spans the start of your search period
  2. has a delivery that spans the end of your search period
  3. has a delivery with a pickup date inside your search period
  4. has a delivery with a delivery date inside your search period

I think that covers all the bases -- only one of the last two is strictly necessary, it covers the case where an existing delivery is completely inside the search period

pala_
  • 8,901
  • 1
  • 15
  • 32
  • Hmm, strange. Your solution works on your SQLFiddle, but it does not work on mine. Probably because the behavior of `between` is different? BTW your solution still includes Sebby on mine. – Keale Jun 05 '15 at 04:15
  • @Keale Can you provide additional sample data then? because as you say, the fiddle works fine. so it must differ from your data in some way. Can you reproduce the failure in sqlfiddle? – pala_ Jun 05 '15 at 04:21
  • So sorry. The date format is wrong. I updated the question and will re-test your answer. – Keale Jun 05 '15 at 04:26
  • @Keale how'd you go re-running it? – pala_ Jun 05 '15 at 04:56
  • I've tried your solution and it worked! Thanks a lot. However other answers have come up and I'm interested in trying them out too. – Keale Jun 05 '15 at 04:59
  • @Keale i can tell your right now that they don't consider all possibilities of overlapping dates. – pala_ Jun 05 '15 at 05:00
  • hah at downvote. some people don't like being told they're wrong i guess. – pala_ Jun 05 '15 at 05:03
  • That's also a possibility. I'll try using more sample data to see. This could take some time... As for the downvote, I noticed it too. Most of the answers got downvoted. I wonder if it is intentional. – Keale Jun 05 '15 at 05:04
  • @Keale i downvoted two of them for being incorrect (one of them a rip off of this answer anyway, just with broken criteria), please see [this fiddle](http://sqlfiddle.com/#!9/647f0/1) showing the difference between the two criteria. i can't be bothered adding in the other answers code because they couldnt even be bothered matching it to your sample field and table names – pala_ Jun 05 '15 at 05:06
  • Actually while i'm on a soapbox, `where in` is a notorious poor performer, and `where not exists` will perform noticeably better. You can check this by creating an index on courier, and then examining the explain plans, [which i've done here](http://sqlfiddle.com/#!9/9685e/1) – pala_ Jun 05 '15 at 05:14
  • @pala_, you told that mine solution is wrong. Please provide an axample where my solution fails. Your solution is working it is just overcomplicating things where there is just a simple fact, that 2 date ranges overlap `if ones startdate <= others enddate and ones enddate >= others startdate`. – Giorgi Nakeuri Jun 05 '15 at 05:46
  • you call it overcomplicating - i call it explaining the criteria - but yes it can be simplified and i jumped the gun a little on yours. – pala_ Jun 05 '15 at 05:49
  • pala_'s solution works too. If ever I come into problems I will try using this one. Thanks for the time and effort spent in entertaining my question @pala_. – Keale Jun 05 '15 at 07:24
0

Your query is somehow related to string to date-time conversion --

As your database table fields doesn't seems the storing the content in datetime or date format --

So your query should be like this --

SELECT courier
  FROM Deliveries
 WHERE STR_TO_DATE(pickup_date, '%m/%d/%Y') > '2015-05-17' OR STR_TO_DATE(pickup_date, '%m/%d/%Y') < '2015-05-16' ;
Abhishek
  • 1,543
  • 3
  • 13
  • 29
  • what? look at his DDL. `pickup_date` and `delivery_date` are both `date` types – pala_ Jun 05 '15 at 04:22
  • @pala_ default format for date field, as I remember in mysql is 'YYYY-MM-DD' , but in your tables data its like 5/18/2015, that's why I told that – Abhishek Jun 05 '15 at 04:24
  • doesn't matter, as it stands the definition and insert statements will fail, it won't store the date in a weird format. – pala_ Jun 05 '15 at 04:25
  • Sorry I was wrong about the date format, updated the question. Anyway, what I provided here is a MCVE. the ones in my real database have the correct date format. – Keale Jun 05 '15 at 04:25
0

You want to know if there exist some row for courier where he is busy and filter out those couriers. This is to find if your date range is overlapping some range by pick and delivery dates:

select distinct d1.courier from Delivers d1
where not exists(select * from Delivers d2
                 where d1.courier = d2.courier and
                 d2.pickDate <= '2015-05-17' and d2.deliveryDate >= '2015-05-16')

There are 9 possible cases of 2 intervals. One can just evaluate predicate with different cases:

enter image description here

http://yetanothermathprogrammingconsultant.blogspot.com/2014/09/when-do-two-intervals-overlap.html

Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
0

Following query will do this for you check out the fiddle

SELECT DISTINCT courier FROM Deliveries WHERE courier NOT IN (
    SELECT courier 
    FROM Deliveries
    WHERE (('2015-05-17' between pickup_date AND delivery_date)
       OR ('2015-05-16' between pickup_date AND delivery_date))
);

EDIT:

SELECT DISTINCT courier FROM Deliveries WHERE courier NOT IN (
  SELECT courier 
  FROM Deliveries
  WHERE (('2015-05-17' between pickup_date AND delivery_date)
      OR ('2015-05-14' between pickup_date AND delivery_date))
      OR ((pickup_date between '2015-05-14' AND '2015-05-17')
      OR ( delivery_date between '2015-05-14' AND '2015-05-17'))
);
Dharmesh Patel
  • 1,881
  • 1
  • 11
  • 12
  • this query fails to take into account all scenarios of overlapping deliveries. – pala_ Jun 05 '15 at 04:59
  • Please give example for which it fails. – Dharmesh Patel Jun 05 '15 at 05:02
  • where the search period completely envelops an existing delivery. [here you go](http://sqlfiddle.com/#!9/647f0/1) broke your fiddle by changing the data and search, and showing the correct result. – pala_ Jun 05 '15 at 05:02
  • check this fiddle with example that encompasses the search period http://sqlfiddle.com/#!9/c1efc/1 – Dharmesh Patel Jun 05 '15 at 05:06
  • @pala_ sorry, I checked the fiddle in you comment and yes, it was wrong, correcting the query with the correct one. Thanks – Dharmesh Patel Jun 05 '15 at 05:13
  • now the only problem is that `where [not] in` is a performance killer, but at least the result should be correct! – pala_ Jun 05 '15 at 05:16