Sql query for available rooms - INN or small hotel for Delphi 2010

Question

I use ADO database in Delphi 2010 (TADOQuery).

The destination is to find the available rooms and show its room rate for a small INN.

t_room

  coderoom as string
  coderoomtype as string

t_typeroom

  coderoomtype as string
  nameroomtype as string
  priceroomtype as number

t_trans

  datetrans as date
  codepoeple as string
  coderoom as string
  dateintrans as date -> date check in 
  dateouttrans as date -> date check out

Currently I use the query below to show the room prices.

SELECT 
    t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, 
    t_roomtype.nameroomtype, t_roomtype.priceroomtype 
FROM 
    t_room 
INNER JOIN 
    t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype 
ORDER BY 
    t_room.coderoom ASC;

And manage to show : (in ADOQuery1 and DBGrid1 in Delphi 2010)

coderoom | nameroomtype | priceroomtype
----------------------------------------
101      | VIP          | 20
102      | VIP          | 20
103      | Standart     | 10
104      | Standart     | 10
105      | Standart     | 10
106      | Standart     | 10

What I want to do is how to show the coderoom s that has not been booked or has not been checkedin within t_trans ? (for specific dates)

Maybe something like below (using NOT IN operator) :

SELECT 
    t_room.coderoom, t_room.coderoomtype, t_room.notesroom, 
    t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype 
FROM 
    t_room 
INNER JOIN 
    t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype 
WHERE 
    t_room.coderoom NOT IN (SELECT * 
                            FROM t_trans 
                            WHERE [current book/checkin/out date not between dateintrans and dateoutrans] 
                            ORDER BY coderoom ASC)  
ORDER BY 
     t_room.coderoom ASC;

The question is how to find available rooms that aren't booked between the t_trans.datein and t_trans.dateout ?

I add some files to make it easy to understand what I want to do at : http://sidhiciang.com/myfiles/TRIAL%20Available%20Rooms.rar

When I use the code below return error : $7701C41F - Exception class EOleException with message "You have writen a subquest that can return more than one field without using EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

The code are :

  AQRoomAvailable1.SQL.Text := 'SELECT t_room.coderoom, t_room.coderoomtype, t_room.notesroom, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype ';
  AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom ';
  AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'NOT IN (SELECT * FROM t_trans x WHERE x.coderoom = t_room.coderoom AND ( (x.dateintrans BETWEEN ' + DateToStr(dtpDateIn1.Date) + ' AND ' + DateToStr(dtpDateOut1.Date) + ' ) ';
  AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'OR (x.dateouttrans BETWEEN ' + DateToStr(dtpDateIn1.Date) + ' AND ' + DateToStr(dtpDateOut1.Date) + ' ) ';
  AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'OR (' + DateToStr(dtpDateIn1.Date) + ' BETWEEN x.dateintrans AND x.dateouttrans) ) )';

I already read links below and did not find the answer and gotten more confused....

check availability of a room with SQL
mysql hotel room availability
listing rooms available[hotel reservation]
query for available rooms in hotel reservation
Select available rooms
selecting room type on room availabilty subquery
Room Booking Query
Room booking sql query
SQL Scheduling - Select All Rooms Available for Given Date Range
SQL Inner-join with 3 tables?
How can I join multiple SQL tables using the IDs?
SQL Query NOT Between Two Dates

bummi · Accepted Answer · 2013-11-17T11:57:23.047

The SQL does not seem to the main problem in this question. To find all lapping ranges you will have to differentiate 4 cases, where case 2 is a special case of case 1 or 3. enter image description here

EE between SW and EW
already caught by case 1 and 3
SE between SW and EW
SW between SE and EE

If you want to use parameters for you query, which you should, it is depending from you database engine if you are able to declare variables in your SQL to avoid the need of using more parameters than needed. An example SQL could look like (depending of the way you are stroring end and start days you might need to add/subtract an offest to your parameters):

Declare @SW datetime
Declare @EW datetime
Select @SW=:SW
Select @EW=:EW
SELECT 
    t_room.coderoom, t_room.coderoomtype,  
    t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype 
FROM 
    t_room 
INNER JOIN 
    t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype 
WHERE 
    t_room.coderoom NOT IN (SELECT x.coderoom 
                            FROM t_trans x
                            WHERE       
                                     (x.dateouttrans between @SW and @EW )
                                 OR  (x.dateintrans between @SW and @EW )
                                 OR  (@SW between x.dateintrans and x.dateouttrans)                                         
                            )  
ORDER BY 
     t_room.coderoom ASC;

EDIT to answer from the comment

Since Access is not capable to use local variables you will have to use 5 parameters, you should not try to create the SQL without parameters. The SQL of AQRoomAvailable1 would look like:

SELECT 
    t_room.coderoom, t_room.coderoomtype,
    t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype 
FROM 
    t_room 
INNER JOIN 
    t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype 
WHERE 
    t_room.coderoom NOT IN (SELECT  x.coderoom
                            FROM t_trans x where

                                     (x.dateouttrans between :SW and :EW )
                                 OR  (x.dateintrans between :SW1 and :EW1 )
                                 OR  (:SW2  between x.dateintrans and x.dateouttrans)

                            )  
ORDER BY 
     t_room.coderoom ASC;

Change the datatype of the parameters to ftDateTime: enter image description here

Change your Action actRoomCheckIn1 to:

procedure TFMain.actRoomCheckIn1Execute(Sender: TObject);
begin
  if (dtpDateOut1.Date >= dtpDateIn1.Date) then
  begin
    AQRoomAvailable1.Close;
    AQRoomAvailable1.Parameters.ParamByName('SW').Value := dtpDateIn1.Date;
    AQRoomAvailable1.Parameters.ParamByName('EW').Value := dtpDateOut1.Date;
    AQRoomAvailable1.Parameters.ParamByName('SW1').Value := dtpDateIn1.Date;
    AQRoomAvailable1.Parameters.ParamByName('EW1').Value := dtpDateOut1.Date;
    AQRoomAvailable1.Parameters.ParamByName('SW2').Value := dtpDateIn1.Date;
    AQRoomAvailable1.Open;
  end
  else
  begin
    AQRoomAvailable1.Active := False;
  end;
end;

maybe you can have a look at http://sidhiciang.com/myfiles/TRIAL%20Available%20Rooms.rar — Galvion, Nov 17 '13 at 11:06

No'am Newman · Answer 2 · 2013-11-16T19:24:53.443

1

Two observations:

It's normal to use integers as primary (and foreign) keys and not strings, as you are doing here
It's not clear to me what the difference is between t_trans.datetrans, t_trans.dateintrans and t_trans.dateouttrans.

If dateintrans is the beginning of the booking and dateouttrans is the end, then the query you need is probably the following

SELECT t_room.coderoom
from t_room
where not exists (select 1 from t_trans
where t_trans.coderoom = t_room.coderoom
and t_trans.dateintrrans >= :p1
and t_trans.dateouttrans <= :p1)

:p1 is the date that which you wish to check.

edited Nov 16 '13 at 19:24

answered Nov 16 '13 at 11:52

No'am Newman

6,395
5
38
50

t_datetrans is the date only for printing invoice purpose. – Galvion Nov 16 '13 at 12:07
any way what is "1" for from SELECT 1 ... ? – Galvion Nov 16 '13 at 12:07
I clearly understand the difference, 1) date of the monetary transaction, 2) check-in time, 3) check-out time – Jerry Dodge Nov 16 '13 at 15:59
@SidhiCiang: the '1' is not relevant. I've seen this written as 'select 'x' from ....'; it's solely necessary for the syntax. – No'am Newman Nov 16 '13 at 18:34

Sql query for available rooms - INN or small hotel for Delphi 2010

2 Answers2