How to exclude date stored in database to a range?

Question

I have a table "Holidays" in my database, that contains a range of days when it's the holidays, defined with two column: start & end.

Holidays (id, name, start, end)

Now, if I have in input, two dates (from & to), I'd like to list all the dates that are not in the holidays.

Suppose the holidays are from 2012/06/05 to 2012/06/20, and I request:

1. from=2012/06/01, to=2012/06/10 ; The result would be 01, 02, 03, 04
2. from=2012/06/01, to=2012/06/22 ; The result would be 01, 02, 03, 04, 21, 22
3. from=2012/06/15, to=2012/06/22 ; The result would be 21, 22

But I can't figure out how to get this list of "opened" days without hitting the database for every days requested in the range from->to.

How could I do that?

Answer 1

There are many solutions, but it pretty much depends on how many entries you have in the database and how many requests you do. If you are making a lot of request, than you can do some thing like this:

-> create a boolean array that will determine if a day is holiday or not;
   first element points to some predefined date (e.g. 1.1.2012), 
   second element to 2.1.2012, etc.
-> initialize an array to 0
-> for each holiday you do
  -> make a for loop initialized with holiday start date and
     expression for each pass: current date = holiday start date + 1 day
    -> covert the current date to index (number of days since start date - 1.1.2012)
    -> set the array[index] to 1

Now you should have a simple array containing 0 for non-holiday day and 1 for holiday day

for each query (request) you now do

-> for loop that goes from request start date to request end date
   -> convert the current date to index (number of days since 1.1.2012)
   -> check if array[index] is 0 or 1

But keep in mid, that this solution is ok for many query (requests). If you have to do the first part for every request, than this solution does not make sense and it is better to write a sql query.

Answer 2

Here's how I finally done that, it seems to work :

SELECT start, end FROM holidays WHERE
(start > :START AND end < :END) OR
(start < :START AND end > :END) OR
(start BETWEEN :START AND :END) OR
(end BETWEEN :START AND :END);

This returns only the rows where my :START/:END dates touch at least one holiday. It covers those possibilities :

1. start is before the begin of an holiday, and end if before the end of an holiday (before, in)
2. start is before the begin of an holiday, and end if after the end of an holiday (before,after)
3. start is after the begin of an holiday, and end if before the end of an holiday (in, in)
4. start is after the begin of an holiday, and end if after the end of an holiday (in, after)

I think I cover all the possibilities with that.

Then I loop over the result and build an array of dates that goes from start to end, for each rows.

And I finally loop over my initial range date, if one of those date is in the array, I remove them.

Answer 3

Here's a solution that can give you the solution in a single (albeit slightly convoluted) SQL statement (this is Oracle):

with all_days as (
  select :start_date + (level - 1) dt
   from dual
  connect by :start_date + (level - 1) <= :end_date
)
select a.dt 
  from all_days a
 where not exists (
    select 1 
      from holidays h
     where h.start_dt <= a.dt and h.end_dt >= a.dt
)
order by a.dt

For example, assuming the following holiday table:

NAME           START_DT                  END_DT                    
-------------- ------------------------- ------------------------- 
Test Holiday 1 07-JUN-12                 13-JUN-12                 
Test Holiday 2 17-JUN-12                 18-JUN-12                 

And using 5th June as :start_date and 20th June as :end_date, you'd get the following output:

DT                        
------------------------- 
05-JUN-12                 
06-JUN-12                 
14-JUN-12                 
15-JUN-12                 
16-JUN-12                 
19-JUN-12                 
20-JUN-12                

(which provides the dates in a range minus any dates specified in a range in the holiday table).

Hope that helps.