CODEWITHSUNDEEP
mysqlsqldate-range
jerrygarciuh
jerrygarciuh

Reputation: 22018

Who had the car during July

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')
    )

Upvotes: 4

Views: 36

Answers (2)

Jim Garrison
Jim Garrison

Reputation: 86774

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

  1. proper ordering of start < end
  2. time ranges are half-open on the right, i.e. the range includes the start instant but not the end instant
  3. null is used for the end date in a range that is not yet finished
  4. 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

Upvotes: 5

jfalkson
jfalkson

Reputation: 3739

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

Upvotes: 0

Related Questions