CODEWITHSUNDEEP
oracle11gcountdate-range
James318
James318

Reputation: 21

Calculating occupany level between a date range

I'm having trouble trying to wrap my head around how to write this query to calculate the occupancy level of a hotel and then list the results by date. Consider the following type of data from a table called reservations:

Arrival     Departure    Guest    Confirmation
08/01/2015  08/05/2015   John     13234
08/01/2015  08/03/2015   Bob      34244
08/02/2015  08/03/2015   Steve    32423
08/02/2015  08/02/2015   Mark     32411
08/02/2015  08/04/2014   Jenny    24422

Output Data would ideally look like:

Date        Occupancy
08/01/2015  2
08/02/2015  4
08/03/2015  2
08/04/2015  1
08/02/2015  0

And the query should be able to utilize a date range as a variable. I'm having trouble getting the obviously hardest piece of how to both get the count per night and spitting it out by date.

Upvotes: 2

Views: 26

Answers (1)

GolezTrol
GolezTrol

Reputation: 116130

You can generate a list of dates first. In Oracle you can do this by using connect by. This will make a recursive query. For instance, to get the next 30 days, you can select today and keep connecting until you've got the desired number of days. level indicates the level of recursion.

select trunc(sysdate) + level - 1 as THEDATE 
from dual
connect by level <= 30;

On that list, you can query the number of reservations for each day in that period:

select THEDATE,
       (select count(*)
        from reservations r
        where r.Arrival >= THEDATE and
              r.Departure < THEDATE) as RESERVATIONCOUNT
from  
  ( select trunc(sysdate) + level - 1 as THEDATE,
    from dual
    connect by level <= 30)

Instead of getting a fixed number of dates, you can also get another value there, for instance, to get at least 30 days in the future, but further if there are reservations for later..:

select THEDATE,
       (select count(*)
        from reservations r
        where r.Arrival >= THEDATE and
              r.Departure < THEDATE) as RESERVATIONCOUNT
from  
  ( select trunc(sysdate) + level - 1 as THEDATE,
    from dual
    connect by 
      level <= greatest(30, (select trunc(max(DEPARTURE) - sysdate)
                             from reservations)))

Upvotes: 1

Related Questions