MSSQL 2008 query to calculate n-th workday from given date

Recently I had need to impose data integrity rule on some entries, which included restriction being effective from third workday of a month. Here is something I've put together to return Nth workday from given date. Customisable national holidays could be defined to include in calculation.

CREATE TYPE [dbo].[CalendarType] AS TABLE(

      [HDate] [datetime] NULL,

      [HName] [varchar](50) NULL

)

GO

 SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[get_workdate](@nth int, @fromDate datetime, @toDate datetime, @holidays CalendarType READONLY, @workdays CalendarType READONLY)

RETURNS DATETIME

AS

BEGIN

  DECLARE @snday int, @stday int

  DECLARE @dt TABLE (d datetime, h int);

  DECLARE @return datetime;   

 

  SELECT

    @snday = CASE @@DATEFIRST

            WHEN 1 THEN 7

            WHEN 2 THEN 6

            WHEN 3 THEN 5

            WHEN 4 THEN 4

            WHEN 5 THEN 3

            WHEN 6 THEN 2

            WHEN 7 THEN 1

      END

  SELECT

    @stday = CASE @@DATEFIRST

            WHEN 1 THEN 6

            WHEN 2 THEN 5

            WHEN 3 THEN 4

            WHEN 4 THEN 3

            WHEN 5 THEN 2

            WHEN 6 THEN 1

            WHEN 7 THEN 7

      END;

  WITH dt (d, h ) as

  (

    select

        @fromDate,

        CASe when (DATEPART(DW, @fromDate) = @snday) OR DATEPART(DW, @fromDate) = @stday  then 1 else 0 END

       

    union all

      select

          DATEADD(day, 1, dt.d),

              CASe when DATEPART(DW, DATEADD(day, 1, dt.d)) = @snday or DATEPART(DW, DATEADD(day, 1, dt.d)) = @stday then 1 else 0 END           

      from dt

      where dt.d <= @toDate

  )

 

  insert @dt

  select *  from dt where d between @fromDate And @toDate

  option (maxrecursion 10000)

 

  select @return = f.d from (

      select *,

      ROW_NUMBER() OVER(ORDER BY h DESC) AS 'r'

      from (

      select dt.d,

            case when h.HDate is not null then 1 else

                  case when w.HDate is not null then 0 else dt.h  end

            end as h

           

      from @dt dt

      left join @holidays h on h.HDate = dt.d

      left join @workdays w on w.HDate = dt.d

      ) as c where h = 0

  ) as f

  WHERE f.r = @nth

  RETURN @return

END

Example usage would be something like that:

declare @holidays CalendarType, @workdays CalendarType

insert @holidays values ('2001-02-24', 'National holiday')

insert @holidays values ('2002-02-24', 'National holiday')

insert @holidays values ('2003-02-24', 'National holiday')

insert @holidays values ('2004-02-24', 'National holiday')

insert @holidays values ('2005-02-24', 'National holiday')

insert @holidays values ('2006-02-24', 'National holiday')

insert @holidays values ('2007-02-24', 'National holiday')

insert @holidays values ('2008-02-24', 'National holiday')

insert @holidays values ('2009-02-24', 'National holiday')

select dbo.get_workdate(30, '2007-04-21', '2008-04-21', @holidays, @workdays)

This should return:

-----------------------

2007-06-01 00:00:00.000

(1 row(s) affected)

This code is working for MSSQL 2008  and above.