Geek Answers Handbook

Who had a car during July

So, I have a table with start, end, taxiand driver. The city now wants to get a historical look at the month that had a cabin for a month, and when any changes occurred.

Thus, it captures assignment lines that started in July 2014, ended in July 2014, or continued (started before July and ended after a month or have not finished yet).

My question is: is there a more efficient or elegant query to get these strings?

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')
    )
+4
source share
2 answers

Checking the overlap of the canonical range:

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

This implies

  • correct order start end
  • , .. ,
  • null , .

a , b ,

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

2012 , . , .

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
0

More articles:


All Articles