SQL - Return days of year with no appointment from table

Posted on 2011-10-24
Last Modified: 2012-05-12
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?
Question by:jamesdean666
    LVL 59

    Accepted Solution

    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.

    FROM dates
    LEFT OUTER JOIN calendar ON calendar.appointmentDate =
    WHERE calendar.appointmentID IS NULL


    FROM dates
       SELECT 1 FROM calendar WHERE calendar.appointmentDate =

    Hope that helps!

    Author Comment

    Thanks for the posts.. bear with me, I have to run, but I'll get back here later today.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
    Viewers will learn how the fundamental information of how to create a table.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now