SQL 查询查找过去的第 3 个工作日，不包括节假日和周末

Question

我正在尝试编写 sql 查询来查找过去的第 3 个工作日，不包括节假日和周末。以下是我的方法，但没有提供正确的解决方案。请建议

第一种方法:

alter function getdate(@givendate datetime)
returns datetime
AS
BEGIN
declare @calcdate datetime
declare @reqdate datetime
declare @count bit
set @count=1
select @calcdate=@givendate-4;
select @reqdate=@calcdate;
while(@calcdate<@givendate)
begin
while(@count=1)
begin
if exists(select 1 from dbo.holidays with(Nolock) where dbo.holidays.date=@reqdate)
begin
select @reqdate=@reqdate-1
end
else
begin
set @count=0
end
end

select @calcdate=@calcdate+1
end
return @reqdate
END

第二种方法:

alter function dbo.getpast3day (@date date)
returns datetime
As
begin
    declare @reqdate datetime;
    declare @Newyears_day datetime;
    declare @Martin_Luther_Kin_Birthday datetime;
    declare @presidents_day datetime;
    declare @Memorial_day datetime;
    declare @Independence_day datetime;
    declare @Labour_day datetime;
    declare @Columbus_day datetime;
    declare @Veterans_day datetime;
    declare @Thanksgiving_day datetime;
    declare @Christmas_day datetime;
    SELECT @reqdate= DATEADD(DAY, CASE (DATEPART(WEEKDAY, @date) + @@DATEFIRST) % 7 
                            WHEN 1 THEN -5 
                            WHEN 2 THEN -6
                            WHEN 3 THEN -6
                            WHEN 4 THEN -6
                            WHEN 5 THEN -6 
                            ELSE -4 
                        END, DATEDIFF(DAY, 0, @date)) ;
--@Martin_Luther_Kin_Birthday                       
    begin                    
        WITH dates AS (
            SELECT DATEADD(day, number, @date) AS theDate
            FROM master.dbo.spt_values
            WHERE type = 'P'
        )
        SELECT @Martin_Luther_Kin_Birthday=theDate
        FROM dates
        WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
            AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21 and DATEPART(month,thedate)=1 and datepart(year,thedate)= year(@date);
    end
    --@presidents_day
    begin                    
        WITH dates AS (
            SELECT DATEADD(day, number, @date) AS theDate
            FROM master.dbo.spt_values
            WHERE type = 'P'
        )
        SELECT @presidents_day=theDate
        FROM dates
        WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
            AND DATEPART(day, thedate)>=15 and DATEPART(day, thedate)<=21 and DATEPART(month,thedate)=2 and datepart(year,thedate)= year(@date);
    end
    --@Memorial_day
    begin                    
        WITH dates AS (
            SELECT DATEADD(day, number, @date) AS theDate
            FROM master.dbo.spt_values
            WHERE type = 'P'
        )
        SELECT @Memorial_day=theDate
        FROM dates
        WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
            AND DATEPART(day, thedate)>=22 and DATEPART(day, thedate)<=31 and DATEPART(month,thedate)=2 and datepart(year,thedate)= year(@date);
    end
    --@Labour_day
    begin                    
        WITH dates AS (
            SELECT DATEADD(day, number, @date) AS theDate
            FROM master.dbo.spt_values
            WHERE type = 'P'
        )
        SELECT @Labour_day=theDate
        FROM dates
        WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
            AND DATEPART(day, thedate)>=1 and DATEPART(day, thedate)<=7 and DATEPART(month,thedate)=9 and datepart(year,thedate)= year(@date);
    end
    --@Columbus_day
    begin                    
        WITH dates AS (
            SELECT DATEADD(day, number, @date) AS theDate
            FROM master.dbo.spt_values
            WHERE type = 'P'
        )
        SELECT @Columbus_day=theDate
        FROM dates
        WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
            AND DATEPART(day, thedate)>=8 and DATEPART(day, thedate)<=14 and DATEPART(month,thedate)=10 and datepart(year,thedate)= year(@date);
    end
    --@Thanksgiving_day
    begin                    
        WITH dates AS (
            SELECT DATEADD(day, number, @date) AS theDate
            FROM master.dbo.spt_values
            WHERE type = 'P'
        )
        SELECT @Thanksgiving_day=theDate
        FROM dates
        WHERE DATEDIFF(day, '19000101', theDate) % 7 = 0
            AND DATEPART(day, thedate)>=22 and DATEPART(day, thedate)<=31 and DATEPART(month,thedate)=11 and datepart(year,thedate)= year(@date);
    end

    if (@reqdate=@Martin_Luther_Kin_Birthday or @reqdate=@presidents_day or @reqdate=@Memorial_day or @reqdate=@Labour_day or @reqdate=@Columbus_day or @reqdate=@Thanksgiving_day)
    begin
        select @reqdate= DATEADD(day,-6,@reqdate)
    end

    --RETURN '2216-09-20 00:00:00.000' 


    return @reqdate

end

Answer 1

不是 100% 清楚过去的第 3 个工作日是什么，但这也许可以提供帮助。显然，您必须决定什么是假期。

Select [dbo].[SomeFunctionName]('2016-01-20',3)

返回

2016-01-15

自定义函数为:

CREATE FUNCTION [dbo].[SomeFunctionName](@Date date,@nDay int)
Returns Date
As
Begin
 Declare @RetVal Date

;with cteHolidays as (
    Select * from (Values 
        ('2016-01-01','New Year''s Day'),
        ('2016-01-18','Martin Luther King, Jr,'),
        ('2016-02-15','Washington''s Birthday'),
        ('2016-03-25','Good Friday'),
        ('2016-05-30','Memorial Day'),
        ('2016-07-04','Independence Day'),
        ('2016-09-05','Labor Day'),
        ('2016-11-24','Thanksgiving'),
        ('2016-11-25','Black Friday'),
        ('2016-12-26','Christmas Day')
        ) as Holidays (Date,Name)
), cteDays as (
Select *
      ,RowNr = Row_Number() over (Order By RetVal Desc)
 From  [dbo].[udf-Create-Range-Date](DateAdd(DD,-3*@nDay,@Date),@Date,'DD',1)
 Where RetVal not in (Select Date from cteHolidays)
   and DatePart(DW,RetVal) between 2 and 6
)
Select @RetVal=RetVal
 From cteDays 
 Where RowNr=@nDay

Return @RetVal

End

它确实需要我用来创建动态日期范围的通用辅助函数

辅助函数

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15) 
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1) 

编辑

验证上面列出的例子

Date        Weekday      BusDayNr
2016-01-20  Wednesday    1        << Selected Date
2016-01-19  Tuesday      2
2016-01-18  Monday       -        << Holiday Martin Luther King, Jr
2016-01-17  Sunday       -
2016-01-16  Saturday     -
2016-01-15  Friday       3        << Date Returned
2016-01-14  Thursday
2016-01-13  Wednesday
2016-01-12  Tuesday
2016-01-11  Monday