How do I get "available" couriers on a specific day in MySQL?
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 schedule looks like this:
Question
The search criteria is a date range and I have to return which courier is "available" in that date range. For example, search criteria from 2015-05-16 - 2015-05-17
. Looking at a table or calendar, it is easy to understand that only Jan and Bart are available on these dates. How do I do this in MySQL?
What i tried
I saw this answer , so I tried it like this:
SELECT courier FROM Deliveries
WHERE pickup_date > '2015-05-17'
OR delivery_date < '2015-05-16'
This gives me Jan and Bart. But this also includes Sebby. This is because while Sebby 2015-05-12 - 2015-05-16
overlaps with the search criteria, this is 2015-05-02 - 2015-05-04
not how it is returned in the query. I need to do something like limiting the request. If the courier schedule overlaps once, then do not return it in the request. I could do it in source code, but I prefer to do all filtering in MySQL. The source code will be the last one.
This popped up on questions that may already have my answer, and this is similar to my previous attempt.
This question (and answer) also speaks to my earlier attempt. Everything else I've seen seems to be related in one way or another, but doesn't answer my question.
Am I on the right track? Or is my approach to the problem wrong?
Scheme
I have provided a script to create a table to save you 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');
source to share
You want to know if there is some line for the courier where he is busy and filter out those couriers. This means that your date range overlaps some range in terms of 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 from two intervals. One can simply evaluate the predicate with different cases:
http://yetanothermathprogrammingconsultant.blogspot.com/2014/09/when-do-two-intervals-overlap.html
source to share
Something like this might work for you. I also suggest you have a different table just for 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'))
)
By using where not exists
this should exclude any courier who:
- has a delivery that covers the beginning of the search period.
- has a delivery that covers the end of the search period.
- has delivery with pickup date in search period
- has delivery with a delivery date in the search period
I think it covers all bases - only one of the last two is strictly needed, it covers the case where the existing delivery is entirely within the search period
source to share
Your query has something to do with converting a string to a date in some way -
Since the fields of your database table don't seem to store the content in datetime format, or
So your request 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' ;
source to share
After running this request, you will check 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'))
);
source to share