SQL Server中的假期表和GetWorkDays函数

create FUNCTION [dbo].[ShiftHolidayToWorkday](@date date)
RETURNS date
AS
BEGIN
    IF DATENAME( dw, @Date ) = 'Saturday'
        SET @Date = DATEADD(day, - 1, @Date)

    ELSE IF DATENAME( dw, @Date ) = 'Sunday'
        SET @Date = DATEADD(day, 1, @Date)

    RETURN @date
END

有些假日正好在某一天，需要调整，有些则在工作日内一天的范围内。

create FUNCTION [dbo].[GetHoliday](@date date)
RETURNS varchar(50)
AS
BEGIN
    declare @s varchar(50)

    SELECT @s = CASE
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-01-01') = _
             @date THEN 'New Year'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]+1) + '-01-01') = _
             @date THEN 'New Year'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-07-04') = _
             @date THEN 'Independence Day'
        WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]  ) + '-12-25') = _
             @date THEN 'Christmas Day'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-12-31') = _
             @date THEN 'New Years Eve'
        --WHEN dbo.ShiftHolidayToWorkday(CONVERT(varchar, [Year]) + '-11-11') = _
             @date THEN 'Veteran''s Day'

        WHEN [Month] = 1  AND [DayOfMonth] BETWEEN 15 AND 21 AND [DayName] = 'Monday' _
              THEN 'Martin Luther King Day'
        WHEN [Month] = 5  AND [DayOfMonth] >= 25             _
              AND [DayName] = 'Monday' THEN 'Memorial Day'
        WHEN [Month] = 9  AND [DayOfMonth] = @EndDate 
        RETURN  0

    DECLARE @Days int
    SET @Days = 0

    IF year(@StartDate) * 100 + datepart(week, @StartDate) = _
       year(@EndDate) * 100 + datepart(week, @EndDate) 
        --same week
        select @Days = (DATEDIFF(dd, @StartDate, @EndDate))
      - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
      - (select count(*) from Holidays where dt between @StartDate and @EndDate)
    ELSE
        --diff weeks
        select @Days = (DATEDIFF(dd, @StartDate, @EndDate) + 1)
      - (DATEDIFF(wk, @StartDate, @EndDate) * 2)
      - (CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
      - (CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
      - (select count(*) from Holidays where dt between @StartDate and @EndDate)
 
    RETURN  @Days
END