0

I have a table of dates, each date represent a task, the task takes three days to complete. I want to select all the unbooked dates that doesn't intersects with another booked task. I've been trying and googling for three days now and I think it is time to ask for help.

date         booked
=========== =======
2014-09-01    0
2014-09-02    1
2014-09-05    0
2014-09-10    1
2014-09-15    0
2014-09-16    0
2014-09-20    1
2014-09-25    0

The expected result:

date         booked
=========== =======
2014-09-01    0
2014-09-15    0
2014-09-16    0
2014-09-25    0
Aus
  • 1,183
  • 11
  • 27
  • can you elaborate the logic behind your expected result set ? – M Khalid Junaid Aug 22 '14 at 08:51
  • The "booked" dates are the dates where a client have booked a task. the dates that are not booked are the requested appoitments. I want to show only the dates that are "not booked" and dose not not happen to be during one of the booked appoitments, given each appointment takes three days to complete. – Aus Aug 22 '14 at 09:01

2 Answers2

1

Refer to the following answer.

Detect overlapping date ranges from the same table

If you could change date to Start_Date and add column End_Date (Start_Date + 3), then adding NOT to the answer quoted will result you in an answer that do not have overlapping date ranges.

select dr1.* from date_ranges dr1
inner join date_ranges dr2
where NOT (dr2.start > dr1.start -- start after dr1 is started
  and dr2.start < dr1.end) -- start before dr1 is finished

From the results of the above query you can select the row which have 0 for booked column.

Community
  • 1
  • 1
codePG
  • 1,754
  • 1
  • 12
  • 19
1

You can use a left join (by adding 3 days in date column) with is null on same table to get the unbooked dates and doesnot intersects with other task's booked date

select t.*,
t1.date date1
from t
left join t t1 on(t.date = t1.date + interval 3 day)
where t.booked = 0 and t1.date is null

Fiddle Demo

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
  • 1
    Thank you, after few changes to your fiddle, this one solved it, including not to intersect with before and after variables: [`Fiddle Demo`][1] - [1]: http://sqlfiddle.com/#!2/637ca9/29 – Aus Aug 22 '14 at 10:31