Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 help with reservation query

Author  Topic 

dude131
Starting Member

6 Posts

Posted - 2005-06-29 : 14:51:58
Hello - I've been trying for the last couple days to revise this MS KB article (http://support.microsoft.com/?kbid=245074) code to work for my database.

Their tables are:
Room -- table name
ID
RoomNum

Booking
ID
Room
Arrival
Checkout

Statements are:
Rooms Reserved
PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout date] DateTime;
SELECT Booking.Room, Booking.Arrival, Booking.Checkout
FROM Booking
WHERE (((Booking.Arrival) Between [Please enter arrival date] And [Please enter checkout date]-1)) Or ((([Checkout]-1) Between [Please enter arrival date] And [Please enter checkout date])) Or (((Booking.Arrival)<[Please enter arrival date]) And (([Checkout]-1)>[Please enter checkout date]-1));

Rooms Available
SELECT Rooms.RoomNum
FROM Rooms LEFT JOIN [Rooms Reserved] ON Rooms.RoomNum=[Rooms Reserved].Room
WHERE ((([Rooms Reserved].Room) Is Null));



My Tables Are:
Booking
BookDateStart
BookDateEnd
BookCustID
BookPropID >> Same as Room Number (Unique)
BookHotelID
BookSubTotal
BookGST
BookPST
BookSurcharge
BookAdults
BookChildren
BookNumberofRooms
BookNotes
BookNum

Property
PropID >> Same as Room Number (Unique)
PropTypeID
PropType
PropRate
PropDesc
PropMaxAdults
PropMaxChildren
PropImg1
PropImg2
PropImg3
PropImg4
PropImg5
PropRoomAmt
PropHotelID



My Statements Are:
PARAMETERS [Please enter arrival date] DateTime, [Please enter checkout date] DateTime;
SELECT Booking.BookPropID, Booking.BookStartDate, Booking.BookEndDate
FROM Booking
WHERE (((Booking.BookStartDate) Between [Please enter arrival date] And [Please enter checkout date]-1)) Or ((([BookEndDate]-1) Between [Please enter arrival date] And [Please enter checkout date])) Or (((Booking.BookStartDate)<[Please enter arrival date]) And (([BookEndDate]-1)>[Please enter checkout date]-1));

SELECT Prop.PropID
FROM Property LEFT JOIN [Rooms Reserved] ON Property.PropID=[Rooms Reserved].PropID
WHERE ((([Rooms Reserved].PropID) Is Null));




-- I cannot get it right -- any ideas where I'm going wrong?

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-29 : 16:01:33
>> to work for my database.

Access or SQL Server ?

Go to Top of Page

dude131
Starting Member

6 Posts

Posted - 2005-06-30 : 12:55:33
access
Go to Top of Page
   

- Advertisement -