Availability in a reservation database

Question

I have a database for hotel reservation with tables: room, customer, reservation (id, id_room, id_customer_ arrive_date, departure_date, ...).

When I select a room in my app I need to view a calendar widget with days red colored if in that day the room is busy.

I need a way to retrieve a list of busy days for a room,month,year combination.

My idea is to create a new table from previous with columns: date,day,month,year,room,is_busy and then query it.

SELECT day FROM new_table WHERE month=m AND year=y AND room=r AND is_busy=1

The problem is to update the new table every time.

Is there a simple way?

You can have a trigger that's set to update the new table whenever a new record gets put in your existing table. [Here's an example](http://stackoverflow.com/questions/2247679/sql-server-trigger-insert-values-from-new-row-into-another-table) of a previous question on SO where a person was looking to grab a couple fields from a new row on insert and put them into a new table. — Dresden, May 05 '16 at 16:30

score 0 · Answer 1 · answered Jun 12 '16 at 16:38

You can try this script. It will return busy dates for each room for next 255 days from now.

SELECT * INTO reservation
FROM (VALUES (1, 1, '2016-07-03','2016-07-06'),(2, 2, '2016-07-10','2016-07-15')) 
a(CustomerID, RoomID, arrive_date, departure_date);
GO
;WITH Pass0 as (select 1 as C union all select 1),
  Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),
  Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),
  Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),
  FutureDates(FutureDates) as (SELECT DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1, CAST(GETDATE() AS DATE)) FROM Pass3)
SELECT r.RoomID, f.FutureDates as BusyDate
FROM reservation as r
INNER JOIN FutureDates as f
ON f.FutureDates >= r.arrive_date and f.FutureDates < r.departure_date;

Availability in a reservation database

1 Answers1