Who had the car during July

Question

So I have a table which has start, end, taxi, and driver. City now wants to get historical look ups by month of who had which cab during month, and when any changes occurred.

So this grabs assignment rows which started in July 2014, ended in July 2014, or were ongoing (started before July and ended after the month or have not yet ended).

My question is whether there is a more efficient or elegant query to get these rows?

SELECT * FROM `taxi_assignments` WHERE 
    (`start` BETWEEN '2014-07-01 00:00:00' AND '2014-07-31 23:59:59')
    OR (`end` BETWEEN '2014-07-01 00:00:00' AND '2014-07-31 23:59:59')
    OR (`start` < '2014-07-01 00:00:00' AND 
        (`end` > '2014-07-31 23:59:59' OR `end` = '0000-00-00 00:00:00')
    )

Why don't you check only month. ie. `(MONTH(start) = 7 OR MONTH(end)=7 OR (MONTH(start) < 7 AND (MONTH(end) > 7 or MONTH(end) = 0))` — Shell, Sep 06 '14 at 03:24
That would get the assignments for July 2012, 2013, etc and they want the records for month X in year Y. — jerrygarciuh, Sep 06 '14 at 03:27
@shell, don't use function like that - it prevents the engine from using any indexes. — Alain Collins, Sep 06 '14 at 03:30
@prix but the data isn't quite like that see http://sqlfiddle.com/#!2/3a60a/1 — jerrygarciuh, Sep 06 '14 at 03:38
@jerrygarciuh I see, then you want Jim's solution which will take into consideration those dates and you would need to switch from `0000-00-00 00:00:00` to `NULL` so that `IS NULL` can properly catch the last one. — Prix, Sep 06 '14 at 03:41

Jim Garrison · Accepted Answer · 2014-09-06T03:42:16.193

5

The canonical range overlap check is:

(a.start < b.end or b.end is null) and (a.end > b.start or a.end is null)

This assumes

proper ordering of start < end
time ranges are half-open on the right, i.e. the range includes the start instant but not the end instant
null is used for the end date in a range that is not yet finished
the start date cannot be null

In your case, a represents the data from the table, while b represents the month of July, so

b.start = 2014-07-01 00:00:00 
b.end   = 2014-08-01 00:00:00

edited Sep 06 '14 at 03:42

answered Sep 06 '14 at 03:30

Jim Garrison

85,615
20
155
190

a and b are not table aliases in your example, correct? – jerrygarciuh Sep 06 '14 at 03:43
1

@jerrygarciuh I guess a `LEFT JOIN` – Prix Sep 06 '14 at 03:44
1

This is a generic solution to determining when two date ranges overlap. I guess for your case `a` is the data from the table and `b` is the range representing July. – Jim Garrison Sep 06 '14 at 03:44
In the first set of parentheses doesn't the `OR b.end IS NULL` allow for any row where `end` is null? Eg (`start` < '2014-6-30 23:59:59' or `end` is null) and (`end` > '2014-6-01 00:00:00' or `end` is null) ORDER BY `start` will select rows which started in August and whose end is NULL, right? – jerrygarciuh Sep 06 '14 at 04:15

score 0 · Answer 2 · answered Sep 06 '14 at 03:39

This would get information from Feb 2012 onwards, as an example. I have also included a field that will tag your taxi as actively in use, or the date it ended.

SELECT Year(start), Month(start),driver, taxi, 
case when end > MONTH(getdate()) or end is null then 'Active' else end
FROM `taxi_assignments` WHERE 
year(start)>2012 and month(start)> 2

Who had the car during July

2 Answers2