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.
SET ANSI_NULLS ON
CREATE TYPE [dbo].[CalendarType] AS TABLE(
[HDate] [datetime] NULL,
[HName] [varchar](50) NULL
)
GO
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.
