Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Query

Status
Not open for further replies.

primagic

IS-IT--Management
Jul 24, 2008
476
GB
I have two tables

tblHosts & tblHolidays

I am trying to get a list of all the HostID's who have no holidays between two dates. Kind of like an availability list.

So far I have

Code:
SELECT     HostID
FROM         dbo.tblHosts
WHERE     (HostID NOT IN
                          (SELECT     HostID
                            FROM          dbo.tblHolidays
                            WHERE      (DateFrom NOT BETWEEN @ArrivalDate AND @DepartureDate) AND (DateTo NOT BETWEEN @ArrivalDate AND @DepartureDate)))

But this seems to retrieve all the hostid's apart from the hosts who have an entry in the holidays table no matter what the dates are.

So I am trying to achieve

Date From: 01/01/2014 DateTo: 30/01/2014

Holiday Tabls

HostID DateFrom DateTo
1 01/02/2014 05/02/2014
2 12/01/2014 15/01/2014
2 15/03/2014 19/03/2014

So my query should return only HostID one because that holiday falls outside the parameters. HostID 2 although has two holidays, one of them falls within the parameters so he would be unavailable for that period.

Hope this makes sense
 
On top off my head:
Code:
SELECT     HostID
FROM   tblHosts
WHERE  HostID NOT IN (SELECT  HostID
                             FROM   tblHolidays
                       WHERE  (DateFrom BETWEEN @ArrivalDate AND @DepartureDate) 
                           OR (DateTo BETWEEN @ArrivalDate AND @DepartureDate))
NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server
 
HI, Try this

Code:
SELECT t.HostID
FROM dbo.tblHosts as t LEFT OUTER JOIN dbo.tblHolidays h
						ON t.HostID = h.HostID
WHERE h.DateFrom <= @DepartureDate 
AND h.DateTo >= @ArrivalDate
AND h.HostID IS NOT NULL
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top