• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 219
  • Last Modified:

SQL - Return days of year with no appointment from table

If I have a simple table (Calendar) that consists of an appointmentID (Primary Key/unique identifier), and the day the appointment is scheduled - How can I return the business days that there are no appointments scheduled for the upcoming year?
0
jamesdean666
Asked:
jamesdean666
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
Simplest way would be to have a table of valid business dates, i.e., excluding holidays or normal non-operation days if, for example, office is closed on Sunday and Monday. You can then find records from you date table that does not have any entries in your Calendar (of appointments) table.

There are multiple alternatives, including LEFT OUTER JOIN with condition of appointmentID IS NULL or using a NOT EXISTS clause.

e.g.,
FROM dates
LEFT OUTER JOIN calendar ON calendar.appointmentDate = dates.date
WHERE calendar.appointmentID IS NULL

OR

FROM dates
WHERE NOT EXISTS (
   SELECT 1 FROM calendar WHERE calendar.appointmentDate = dates.date
)

Hope that helps!
0
 
jamesdean666Author Commented:
Thanks for the posts.. bear with me, I have to run, but I'll get back here later today.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now