Help with Query

[primagic]

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

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

 

[bborissov]

On top off my head:

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

 

[imex]

Try:

SELECT t.HostID
FROM dbo.tblHosts as t
WHERE
    not exists (SELECT d.HostID
                FROM dbo.tblHolidays as d
                WHERE
                    d.HostID = t.HostID AND
                    d.DateFrom <= @DepartureDate AND
                    d.DateTo >= @ArrivalDate)

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[Motlatjo]

HI, Try this

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