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
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
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