[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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
  • 2
  • 2
1 Solution
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.

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.

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.

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