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.
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.HotelIDFROM Hotels hWHERE NOT EXISTS ( SELECT * FROM BookedDays b WHERE b.HotelId = h.HotelId AND b.BookedDate BETWEEN @CheckIn AND @CheckOut ); |
|
|
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. |
|
|
yorke19
Starting Member
3 Posts |
Posted - 2013-01-21 : 20:30:08
|
This is query which work i make it :DSelect 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+"') |
|
|
|
|
|