Excluding Holidays

Posted on 2006-04-18
Last Modified: 2012-06-27
I have the following code that works well for excluding weekends, thanks to the experts here.

(datediff(dd,a.created_date, @Today) - ((datediff(ww,a.created_date, @Today)*2))) > 2

Now I need to exclude holidays as well. Our databse has a table for Holidays. What's the best way to exclude a possible holiday?

Holiday                                      Holiday_name
2006-01-02 00:00:00.000      New Year's Day
2006-01-16 00:00:00.000      Martin Luther King Jr. Birthday
2006-02-20 00:00:00.000      President's Day
2006-05-29 00:00:00.000      Memorial Day
2006-07-04 00:00:00.000      Independence Day
2006-09-04 00:00:00.000      Labor Day
2006-11-10 00:00:00.000      Veteran's Day
2006-11-23 00:00:00.000      Thanksgiving
2006-11-24 00:00:00.000      Day after Thanksgiving
2006-12-25 00:00:00.000      Christmas Day
Question by:SQLMAN1951
    LVL 2

    Expert Comment

    Can you post more background on your query? Is this a select statement to insert holidays into your holday table?

    Author Comment

    This is part of my where clause to get cases that  are more than 2 days old. We don't want to consider a case overdue counting weekends and holidays. So I was looking for a way to incorporate the table of holiday information into the where clause for exclusion.

    (datediff(dd,a.created_date, @Today) - ((datediff(ww,a.created_date, @Today)*2))) > 2
    LVL 2

    Accepted Solution

    if object_id('fn_getholidaycount') is not null
      drop function fn_getholidaycount

    create function fn_getholidaycount
       @start_date as datetime,
       @end_date as datetime
    returns int
      declare @holiday_count int

      select @holiday_count = count(*)
        from holiday
       where holiday_date >= @start_date
           and holday_date <= @end_date

      return @holiday_count

    Now... in your where clause, you can reference this function as follows:

    (datediff(dd,a.created_date, @Today) - ((datediff(ww,a.created_date, @Today)*2))) > (2 + dbo.fn_getholidaycount(a.created_date, @today))

    ** WARNING **
    Doing a ranged comparison between dates like I did in the above function may cause performance issues depending on how you've indexed the table. You may want to test this solution out to make sure it works within the performance requirements you have. Given this approach, though, your logic for determining the number of holidays that occur between the 2 dates supplied is abstracted out so you can play with the way the function works to improve performance over time. You may actually have better performance by hard-coding the logic of whether or not a date is a holiday right into the function based on the month and day.

    LVL 28

    Expert Comment

    Change your where clause to

    (datediff(dd,a.created_date, @Today) - ((datediff(ww,a.created_date, @Today)*2))) > 2
    convert(datetime,convert(varchar(10), a.created_date,101),101) not in (select Holiday from Holidays)

    Author Comment


    I just finished testing your solution and it is awesome. Thank you.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    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…
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now