-4

Please accept my apologies for a lack of attempted code.

I can't get my head around this - or even figure out if it's feasible!

I have a table filled with 'date slots', these can be booked by an individual.

The time slots table looks like this:

ID | Price | Available_from | Available_ to    
------------------------------------
 1 | 20.00 | 2017-10-01 | 2018-01-01
 2 | 20.00 | 2017-11-01 | 2017-12-07
 3 | 20.00 | 2017-10-31 | 2018-01-31
 4 | 20.00 | 2017-10-22 | 2017-11-21    
------------------------------------

In these rows, there is a common date range where all four results are available, 2017-11-01 > 2017-11-21

I would like to query my database to see if 1) there is a common range for every result and 2) if there is, get the date range.

Desperate for help!

Thank you

Strawberry
  • 33,750
  • 13
  • 40
  • 57
user3061608
  • 21
  • 1
  • 7
  • Well if you just look at your own example data, it should become obvious quite quickly, that the desired result consists of the maximum Available_from date and the minimum Available_ to date. (As for getting those, go research "mysql groupwise maximum" for the basic idea.) Of course it won't be as simple as that, if there might be "holes" in the existing intervals - but your example did not contain such a case. So now your next step is to think about what the "edge cases" in this situation will be, and what specific handling they might need. – CBroe Oct 12 '17 at 12:18
  • Look at http://salman-w.blogspot.ca/2012/06/sql-query-overlapping-date-ranges.html. Also research StackOverflow for sql date range intersection, you will find some detailed answers. Ex https://stackoverflow.com/questions/4490553/detect-overlapping-date-ranges-from-the-same-table – Nic3500 Oct 12 '17 at 12:20
  • Thanks @CBroe, having laid it out like that and had some sanity added - yes, it seems far simpler! Thank you, I'll explore that now. – user3061608 Oct 12 '17 at 12:21

1 Answers1

1

I think this would work :

SELECT  MAX(Available_from) as MatchFrom, 
        MIN(Available_to) as MatchTo
FROM Temporary.test
WHERE 
  (SELECT MAX(Available_from) FROM Temporary.test)
  <=(SELECT MIN(Available_to) FROM Temporary.test)

It will return NULL, if there is no overlapping range.

Nenos
  • 129
  • 1
  • 2
  • 10