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.