Help in hotel search query
-
I am trying to search hotels,i am stuck with this particular search criteria where
you have to search based on Rooms Where u can assign how many adults,child,extrabed u want to assign in each room.
for eg
Adult child extrabed
Room1 1 0 0
Room2 2 1 0
u can see this criteria in booking.com, there extrabed is not there
This way the search criteria come,i am storing this in table value parameter
CREATE TYPE [dbo].[udt_PassengerType] AS TABLE(
[ud_Adult] [tinyint] NOT NULL,
[ud_Child] [tinyint] NOT NULL,
[ud_Extrabed] [tinyint] NOT NULL
)
There is two tables
Hotel table
Room table
DECLARE @Hotel TABLE(HotelID int,HotelName varchar(150))
INSERT INTO @Hotel (HotelID,HotelName)
SELECT 1,'Kingston hotel'
UNION ALL
SELECT 2,'Sydney hotel'
UNION ALL
SELECT 3,'London hotel'
--SELECT * FROM @Hotel
DECLARE @Room TABLE(RoomID int,Roomname varchar(100),HotelID int,IncludedAdult int,IncludedChild int, MaxAdult int,MaxChild int,MaxOccupancy int)
INSERT INTO @Room(RoomID,Roomname,HotelID,IncludedAdult,IncludedChild, MaxAdult,MaxChild,MaxOccupancy)
SELECT 1,'Kingston Single room',1,1,1,1,1,2 --MaxOccupancy = MaxAdult + MaxChild
UNION ALL
SELECT 2,'Kingston Double ROOM',1,2,0,2,0,2
UNION ALL
SELECT 3,'Sydney Single ROOM',2,1,1,1,1,2
UNION ALL
SELECT 4,'Sydney Single+ExtraBed ROOM',2,1,1,2,1,3--Extrbed = Max Adult - IncludedAdult = 2-1=1
UNION ALL
SELECT 5,'Sydney Double ROOM',2,2,0,2,0,2
UNION ALL
SELECT 6,'Sydney Double+ExtraBed ROOM',2,2,1,3,1,4 --Extrbed =3-2 =1
UNION ALL
SELECT 7,'Sydney TripleBed ROOM',2,3,0,3,0,3
UNION ALL
SELECT 7,'Sydney Triple+ExtraBed ROOM',2,3,0,5,0,5 --Extrbed =5-3 =2
--SELECT RoomID,Roomname,HotelID,IncludedAdult,IncludedChild, MaxAdult,MaxChild,MaxOccupancy,MaxAdult - IncludedAdult AS ExtraBed FROM @Room
Included Adult means how many adult are assigned to room
Max Adult means total no of adults that room can occupy
Extrabed = Max Adult - IncludedAdult
There is some coditions
If ur searching for 1 adult then u have to show the result single,double,triple,quad room etc
meaning it should give rooms which can accomodate greater than and equal to 1
if u serch for adults it should give room greater than and equal to 2
this will display the following rooms Double, triple,Quad,double+extrabed
there is no single bed but it can show single+extrabed
here i am searching for 3 adult in only one room, it bring the correct results
DECLARE @P [dbo].[udt_PassengerType]
INSERT INTO @P VALUES(3,0,0)
--INSERT INTO @P VALUES(2,0,1)
--INSERT INTO @P VALUES(5,0,1)
DECLARE @MinAdultCount tinyint
DECLARE @MinChildCount tinyint
DECLARE @ExtrabedCount tinyint
SELECT @MinAdultCount = MIN(ud_Adult) FROM @P
SELECT @MinChildCount = MIN(ud_Child) FROM @P
SELECT @MinChildCount = CASE
WHEN MIN(ud_Child)= 0 THEN NULL
ELSE MIN(ud_Child) END FROM @P
SELECT @ExtrabedCount = CASE
WHEN MIN(ud_Extrabed)= 0 THEN NULL
ELSE MIN(ud_Extrabed) END FROM @P
SELECT
RoomID,
Roomname,
HotelID,
IncludedAdult,
IncludedChild,
MaxAdult,
MaxChild,
MaxOccupancy
FROM @RoomAS SP
WHERE (@MinAdultCount <= SP.MaxAdult AND (@MinChildCount IS NULL OR @MinChildCount <= SP.MaxChild)AND(@ExtrabedCount IS NULL OR @ExtrabedCount < SP.MaxAdult))
Now i am searching in 2 rooms, the result is correct
DECLARE @P [dbo].[udt_PassengerType]
INSERT INTO @P VALUES(1,0,0)
INSERT INTO @P VALUES(2,1,1)
--INSERT INTO @P VALUES(5,0,1)
DECLARE @MinAdultCount tinyint
DECLARE @MinChildCount tinyint
DECLARE @ExtrabedCount tinyint
SELECT @MinAdultCount = MIN(ud_Adult) FROM @P
SELECT @MinChildCount = MIN(ud_Child) FROM @P
SELECT @MinChildCount = CASE
WHEN MIN(ud_Child)= 0 THEN NULL
ELSE MIN(ud_Child) END FROM @P
SELECT @ExtrabedCount = CASE
WHEN MIN(ud_Extrabed)= 0 THEN NULL
ELSE MIN(ud_Extrabed) END FROM @P
SELECT
RoomID,
Roomname,
HotelID,
IncludedAdult,
IncludedChild,
MaxAdult,
MaxChild,
MaxOccupancy
FROM @RoomAS SP
WHERE (@MinAdultCount <= SP.MaxAdult AND (@MinChildCount IS NULL OR @MinChildCount <= SP.MaxChild)AND(@ExtrabedCount IS NULL OR @ExtrabedCount < SP.MaxAdult))
Now i am searching for one room with 2 adult and 1 extra bed
but the result is not correct as it gives Kingston Double ROOM,Sydney Single+ExtraBed ROOM,Sydney Double ROOM
as these romms cannot accomodate 2 adults and 1 extrabed
DECLARE @P [dbo].[udt_PassengerType]
INSERT INTO @P VALUES(2,0,1)
--INSERT INTO @P VALUES(2,1,1)
--INSERT INTO @P VALUES(5,0,1)
DECLARE @MinAdultCount tinyint
DECLARE @MinChildCount tinyint
DECLARE @ExtrabedCount tinyint
SELECT @MinAdultCount = MIN(ud_Adult) FROM @P
SELECT @MinChildCount = MIN(ud_Child) FROM @P
SELECT @MinChildCount = CASE
WHEN MIN(ud_Child)= 0 THEN NULL
ELSE MIN(ud_Child) END FROM @P
SELECT @ExtrabedCount = CASE
WHEN MIN(ud_Extrabed)= 0 THEN NULL
ELSE MIN(ud_Extrabed) END FROM @P
SELECT
RoomID,
Roomname,
--HotelID,
IncludedAdult,
IncludedChild,
MaxAdult,
MaxChild,
MaxOccupancy
FROM @RoomAS SP
WHERE (@MinAdultCount <= SP.MaxAdult AND (@MinChildCount IS NULL OR @MinChildCount <= SP.MaxChild)AND(@ExtrabedCount IS NULL OR @ExtrabedCount < SP.MaxAdult))
Now for 3 rooms with the following condition it should not give results as there is adult =7 which is not i the table so there woulb no rooms available eventhough in room 1 and room 2 has satisfying condition
DECLARE @P [dbo].[udt_PassengerType]
INSERT INTO @P VALUES(1,0,0)
INSERT INTO @P VALUES(2,0,0)
INSERT INTO @P VALUES(7,0,1)
DECLARE @MinAdultCount tinyint
DECLARE @MinChildCount tinyint
DECLARE @ExtrabedCount tinyint
SELECT @MinAdultCount = MIN(ud_Adult) FROM @P
SELECT @MinChildCount = MIN(ud_Child) FROM @P
SELECT @MinChildCount = CASE
WHEN MIN(ud_Child)= 0 THEN NULL
ELSE MIN(ud_Child) END FROM @P
SELECT @ExtrabedCount = CASE
WHEN MIN(ud_Extrabed)= 0 THEN NULL
ELSE MIN(ud_Extrabed) END FROM @P
SELECT
RoomID,
Roomname,
--HotelID,
IncludedAdult,
IncludedChild,
MaxAdult,
MaxChild,
MaxOccupancy
FROM @RoomAS SP
WHERE (@MinAdultCount <= SP.MaxAdult AND (@MinChildCount IS NULL OR @MinChildCount <= SP.MaxChild)AND(@ExtrabedCount IS NULL OR @ExtrabedCount < SP.MaxAdult))
there will be condition for adult child, extrabed
if any further info need pls let me know
-
You need to give us some more clear rules. Why is searching for a 2 adult room any different from searching for a 3 adult room? I don't follow the reason why you separated those?
-
All the querys are same except the insert statement for table value parameter...
i just created the search scenarios... for 3 rooms there will be 3 insert statement
for 2 rooms there will be 2 insert statement
-
This is the image for searching 5 rooms with different adult,child,extrabed combination..i want to get from min adult and child combination to the maximum condition available.
If any room has adult numbe which doesn't satisfy the Max adult count in room detail then it should nor display any result
-
I can't see your image, probably the firewall here. I don't really understand what you're trying to do either. It's either really simple or really complicated - I can't tell. I suspect that you're making it more complicated than it needs to be. Multiple conditions on a where clause are a fairly common thing.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply