SQL query to exclude records if it matches an entry in another table (such as holiday dates)
THe following query should get you a list of applications that DO NOT have a holiday defined for the CURRENT date.
SELECT apps.ApplicationName, apps.isavailable FROM dbo.Applications appsWHERE apps.ApplicationName = @AppName AND NOT EXISTS ( SELECT * FROM Holidays WHERE ApplicationId = apps.ApplicationId AND CONVERT(VARCHAR,getdate(),101) = CONVERT(VARCHAR,holidaydate,101))
Basically what we do is select everything where it does not have a match.
OK, just to be different, how about something like this:
select apps.isavailablefrom dbo.Application apps left outer join dbo.Holidays hol on apps.applicationid = hol.applicationid and convert(varchar(10),getdate(),101) = convert(varchar(10),hol.holidaydate,101)where apps.applicationname = @appname and hol.applicationid is null
Basically, you're joining the tables based on applicationid and the current date. Since it's a left join, you'll always get all the applications that match @appname, then you just filter out any results that get a match based on the holiday date being the current date. Assuming that applicationname is unique, you'll always get a single row where the right half of the join is null, unless the current date matches a holiday, in which case the query will return no results.
I don't know how it stacks up with the other solutions performance-wise; I believe joins are generally supposed to be faster than sub-queries, but that probably depends on a variety of factors, so YMMV.
You can use "WHERE NOT EXISTS":
SELECT *FROM Applications aWHERE NOT EXISTS ( SELECT * FROM Holidays h WHERE h.ApplicationID = a.ApplicationID AND HolidayDate = cast(cast(getdate() as int) as datetime))
I'm doing the cast there to truncate the getdate() call back to just the date. Haven't tested that exact query but I think it'll do the job for you.