Excluding Holidays

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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Can you post more background on your query? Is this a select statement to insert holidays into your holday table?
SQLMAN1951Author Commented:
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
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))

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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)
SQLMAN1951Author Commented:

I just finished testing your solution and it is awesome. Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.