如何在SQL Server查询中排除周末和节假日 [英] How can I exclude weekends and holidays in SQL Server query

问题描述

我正在创建一个查询，该查询将调整日期，不包括节假日和周末。

I'm creating a query which will adjust the date excluding holidays and weekends.

示例数据：

调整日期|调整日期（不包括节假日和周末）

02/06/16 | 16年2月9日

在我的示例中，日期为周末，调整后的日期为2月9日，因为2月8日为假期，所以它需要进行调整，以使调整后的日期成为一个工作日。目前，我有一个会计年度所有周末和节假日的单独表。

On my example, The date is a weekend and adjusted date becomes Feb 9 because Feb 8 is a holiday, so it needs to adjust so that the adjusted date would be a working day. Currently, I have a separated table of all the weekends and holidays in a fiscal year.

select  case when (
               select   count(dbo.WeekendsHoliday.[Weekends & Holidays])
               from     dbo.WeekendsHoliday
               where    dbo.WeekendsHoliday.[Weekends & Holidays] 
                    = case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                           then dateadd(day, 1, [Date Received]) 
                           else [Date Received] 
                      end
              ) > 0 
         then case (datename(DW, 
                             case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end))
                when 'Saturday'
                then dateadd(day, 2, 
                             case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end)
                else dateadd(day, 1, 
                             case when convert(time, [Time Received]) > convert(time, '5:00:00 PM') 
                                  then dateadd(day, 1, [Date Received])
                                  else [Date Received] 
                             end)
              end
    end as [Adjusted Date Excluding holidays and weekends]

如果假日是连续2天（星期四和星期五），那么调整后的日期将是星期六，因为它是周末，所以仍然无效。

What happens here is if the holiday is 2 consecutive days (Thursday and Friday), adjusted date would be Saturday which is still not valid because it's a weekend.

调整后的日期是这里的别名

推荐答案

我建议创建一个函数，该函数基于以下条件递归验证下一个工作日包含周末和节假日的表格。这种方法的优点是，它在需要时可以重用。

I suggest to create a function that recursively verify the next working day based on the table that contains weekends and holidays. The advantage of this approach is that it is a reusable function whenever you need it.

此函数接收日期和时间。 （根据您问题中的代码）如果时间在下午5点之后，则增加一天。之后，继续检查日期是否在周末或节假日之内，直到找到下一个工作日为止：

This function receives the date and time. (Based on the code in your question) if the time is after 5pm, adds a day. After, continues checking if the date is not within weekends or holidays until find the next working day:

CREATE FUNCTION dbo.adjustedDate(@dateReceived DATETIME, @timeReceived TIME)
RETURNS DATETIME
AS
BEGIN
    DECLARE @adjustedDate DATETIME = @dateReceived

    -- Verify time to add 1 day to @adjustedDate
    IF @timeReceived IS NOT NULL
        IF @timeReceived > CONVERT(TIME, '5:00:00 PM')
             SET @adjustedDate = DATEADD(DAY, 1, @adjustedDate)

    -- Continue adding 1 day to @adjustedDate recursively until find one date that is not a weekend or holiday
    IF EXISTS(SELECT [Weekends & Holidays]
                FROM dbo.WeekendsHoliday
                WHERE [Weekends & Holidays] = @adjustedDate)                
        SET @adjustedDate = dbo.adjustedDate(DATEADD(DAY, 1, @adjustedDate), NULL)

    RETURN @adjustedDate
END

这篇关于如何在SQL Server查询中排除周末和节假日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！