SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Hotel Search Engine for Not Booked Days
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

yorke19
Starting Member

3 Posts

Posted - 01/21/2013 :  12:48:13  Show Profile  Reply with Quote
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.http://www.booking.com/,http://www.agoda.it/ .I will insert photo with tables in my database and layout of search engine.

Edited by - yorke19 on 01/21/2013 12:50:49

James K
Flowing Fount of Yak Knowledge

3589 Posts

Posted - 01/21/2013 :  15:24:13  Show Profile  Reply with Quote
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 - 01/21/2013 :  19:23:55  Show Profile  Reply with Quote
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 - 01/21/2013 :  20:30:08  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000