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
 General SQL Server Forums
 New to SQL Server Programming
 Hotel Search Engine for Not Booked Days

Author  Topic 

yorke19
Starting Member

3 Posts

Posted - 2013-01-21 : 12:48:13
I don't know how to make good SQL query for my search engine.I have table with booked dates for every hotel.In search engine i have 2 fields for Check-In date and Check-Out date.I should make SQL query who will search in database in Booked days table and select hotels which have free days.This is standard search engine for all websites for hotel reservations.(ex.[url]http://www.booking.com/[/url],[url]http://www.agoda.it/)[/url].I will insert photo with tables in my database and layout of search engine.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 15:24:13
Can you try this?
DECLARE @Checkin DATETIME, @Checkout DATETIME;
SET @CheckIn = '20130201';
SET @Checkout = '20130207';
SELECT
h.HotelID
FROM
Hotels h
WHERE
NOT EXISTS
(
SELECT * FROM BookedDays b
WHERE b.HotelId = h.HotelId
AND b.BookedDate BETWEEN @CheckIn AND @CheckOut
);
Go to Top of Page

yorke19
Starting Member

3 Posts

Posted - 2013-01-21 : 19:23:55
no this doesn't help me.it return hotels which doesn't have dates in the check in - check out range.Some hotels maybe have few dates in the check in/out range but not all days,there are some not booked days in that range so function should return that hotel too.SQL query should remove or didn't select hotels which have all day booked in the selected range.if there is only one available that the hotelshould be selected.
Go to Top of Page

yorke19
Starting Member

3 Posts

Posted - 2013-01-21 : 20:30:08
This is query which work i make it :D
Select Email FROM Users WHERE Email not in ( Select U.Email FROM Users U,EventsTable E WHERE U.Email=E.HotelID AND EventDate >= '" + startDate + "' AND EventDate <= '" + endDate + "' GROUP BY U.Email HAVING COUNT(E.HotelID) = '"+NumberOfDaysInRange+"')
Go to Top of Page
   

- Advertisement -