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

Question

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?

Solution 1

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.

Solution 2

OK, just to be different, how about something like this:

select apps.isavailable
from 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.

Solution 3

You can use "WHERE NOT EXISTS":

SELECT *
FROM Applications a
WHERE 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.

Solution 4

Do something like this:

SELECT (fields) FROM Application
WHERE NOT EXISTS
  (SELECT * FROM Holidays
   WHERE ApplicationID = Application.ApplicationID
   AND DAY(getdate()) = DAY(holidaydate) 
   AND MONTH(getdate()) = MONTH(holidaydate)
   AND YEAR(getdate()) = YEAR(holidaydate)
  )

Of course it would be a whole lot easier and faster with SQL Server 2008's "DATE" datatype, or if you could store day, month, year in "Holidays" separately.

Marc

Solution 5

SELECT a.isAvailable
  FROM Application a
 WHERE NOT EXISTS (
    SELECT TOP 1 0
      FROM Holidays b
     WHERE a.applicationid = b.applicationid
       AND holidaydate = $today
 )

View more solutions

Comments

[Tai Squared]

This gives all applications that don't have a holiday defined, but I need the isavailable flag for only the specified apps.applicationname. I don't want to have to iterate through the list after the query. Also, what if I have to join on another table - I can't combine the where and where not clause

[Tai Squared]

This has the same problem as Michael Sellers' answer - This gives all applications that don't have a holiday defined, but I need the isavailable flag for only the specified apps.applicationname.

[Mitchel Sellers]

Matt - In most cases the performance is about the same. It looks like this user is needing just a single application, so the subquery route might be a bit faster...

[Mitchel Sellers]

I updated the example for you to show you how to limit by app name, assuming a param of @AppName

[Tai Squared]

That works - thanks. Can you please edit it to include one more closing paren so someone can cut and paste it?

[Matt Hamilton]

Sorry - didn't think that would be a problem since your original query already included the test for ApplicationName = @appname. I figured you knew how to do that.

[Matt]

Mitchel Sellers - I always thought the issue with subqueries was that they were repeated for every row, but I see what you're saying. All I really know about performance is that if it matters in a given case I should just measure it. ;)

[Matt]

And the "where not exists" approach is certainly more readable in any case.

[Mitchel Sellers]

Tai - Sure did! (I don't know where it went in the beginning!)