Databases
StackOverflow2013
Postsdbo
POSQL query to exclude records if it matches an entry in another table (such as holiday dates)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query to exclude records if it matches an entry in another table (such as holiday dates)
    text
    copied!<p>I have two tables:</p> <blockquote> <p><strong>Application</strong><br> applicationid (int)<br> applicationname (varchar)<br> isavailable (bit)</p> </blockquote> <p>and </p> <blockquote> <p><strong>Holidays</strong><br> applicationid (int)<br> holidaydate (datetime) </p> </blockquote> <p>I need to get the <em>isavailable</em> flag for any given applicationname but it should only return if the day if is not a holiday. The <em>isavailable</em> flag is independent of holidays - it is only set if there are system-wide problems, not on a set schedule. </p> <p>I initially had something like:</p> <pre><code>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) </code></pre> <p>but that was returning records even if today was a holiday because the other holiday dates don't equal today.</p> <p>I tried </p> <pre><code>and (CONVERT(VARCHAR,getdate(),101)) not in (CONVERT(VARCHAR,hol.holidaydate,101)) </code></pre> <p>(it is on SQL Server 2005, so there is no Date type so I have to convert it)</p> <p>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?</p> <p><strong>Update</strong> </p> <p>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.</p> <p>Also, what if I added a table like:</p> <blockquote> <p><strong>HoursOfOperations</strong><br> applicationid (int)<br> mondayopen (datetime)<br> mondayclose (datetime)<br> tuesdayopen (datetime)<br> tuesdayclose (datetime)<br> //open and close for all seven days of the week </p> </blockquote> <p>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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload