Calculate Previous Business Day Exclude Saturday, Sunday and Holiday

• kashif.special2005

  SSC Veteran

  Points: 249

  More actions

  June 12, 2013 at 4:41 am

  #394950

  Hi,

  My name is Kashif, I am very new in sql server, I want create a UDF function that will return a previous date (exclude saturday, sunday and holiday)

  Holiday's list in a table called 'tblHoliday'

  Please help me to get desired result.

  Thanks

  Kashif

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  June 12, 2013 at 6:45 am

  #1623314

  What input will you be passing to the UDF?

  It would be helpful if you can us the DDL of the table "tblHoliday" as well.

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  June 12, 2013 at 10:55 am

  #1623438

  You would need to do something like this:

  -- (1) SAMPLE DATA SETUP

  IF OBJECT_ID('tempdb..#tblHoliday') IS NOT NULL

  DROP TABLE #tblHoliday;

  IF OBJECT_ID('tempdb..#dates') IS NOT NULL

  DROP TABLE #dates;

  --Holiday table, I only added New Years, Christmas and Christmas Eve

  CREATE TABLE #tblHoliday (dt_id int identity primary key, dt_date date not null);

  CREATE TABLE #dates (dt_id int primary key, dt_date date not null, dt_day varchar(10) not null);

  INSERT INTO #tblHoliday

  SELECT '1/1/2013' UNION ALL SELECT '12/24/2013' UNION ALL SELECT '12/25/2013';

  -- (2) Below is the param you would pass

  DECLARE @dateToEvaluate date='12/26/2013';

  -- (3) Routine

  DECLARE @startDate date=CAST('1/1/'+CAST(YEAR(@dateToEvaluate) AS char(4)) AS date); -- let's get the first of the year

  WITH

  tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns),

  dates AS (

  SELECTROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS dt_id,

  DATEADD(DAY,n,@startDate) AS dt,

  DATENAME(WEEKDAY,DATEADD(DAY,n,@startdate)) AS dt_name

  FROM tally

  WHERE n<366 --arbitrary

  AND DATEPART(WEEKDAY,DATEADD(DAY,n,@startDate)) NOT IN (1,7)

  AND DATEADD(DAY,n,@startDate) NOT IN (SELECT CAST(dt_date AS date) FROM #tblHoliday)),

  curr_id(id) AS (SELECT dt_id FROM dates WHERE dt=@dateToEvaluate)

  SELECT d.*

  FROM dates AS d

  CROSS JOIN

  curr_id c

  WHERE d.dt_id+1=c.id

  --Cleanup

  DROP TABLE #dates;

  DROP TABLE #tblHoliday;

  This is an example of how to get the previous date excluding weekends and dates added to the #tblHoliday' table.

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001