SQL query to exclude records if it matches an entry in another table (such as holiday dates)

joinsqlsql-server-2005

I have two tables:

Application
applicationid (int)
applicationname (varchar)
isavailable (bit)

and

Holidays
applicationid (int)
holidaydate (datetime)

I need to get the isavailable flag for any given applicationname but it should only return if the day if is not a holiday. The isavailable flag is independent of holidays – it is only set if there are system-wide problems, not on a set schedule.

I initially had something like:

select top 1 apps.isavailable
from dbo.Applications apps, dbo.Holidays hol
where apps.applicationid = hol.applicationid and
      apps.applicationname = @appname and
      ((datediff(dd,getdate(),hol.holidaydate)) != 0)

but that was returning records even if today was a holiday because the other holiday dates don't equal today.

I tried

and (CONVERT(VARCHAR,getdate(),101)) not in (CONVERT(VARCHAR,hol.holidaydate,101))

(it is on SQL Server 2005, so there is no Date type so I have to convert it)

but again, it was returning records even if today was a holiday. How can I structure this query using a "not in" or "except" clause (or something else) to only return a record if today isn't a holiday?

Update

I don't need a list of all applicationnames that don't have a holiday – I need a record for the specified apps.applicationname. The answers below only return the application names that don't have a holiday on today. The query should return the isavailable flag if it is not a holiday, or else return no records if it is a holiday. I don't care about the other applications.

Also, what if I added a table like:

HoursOfOperations
applicationid (int)
mondayopen (datetime)
mondayclose (datetime)
tuesdayopen (datetime)
tuesdayclose (datetime)
//open and close for all seven days of the week

Could I join on all three of these tables to only return a record if it is within the hours for the given day and is not a holiday? Do I have to do this in separate queries?

Best Solution

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 apps
WHERE 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.