如何在SQL中提取两个日期之间的公共假期并计算工作日和工作小时数

Question

我是SQL新手，遇到了一些复杂的查询问题。

我的目标是计算两个时间戳字段之间以下两种类型的总天数：

1. 工作日数（不包括周末和公共假期） 2. 总天数（包括周末和公共假期）

计算条件如下：

- 如果OrderDate时间<=12:00 PM，则从0开始计数 - 如果OrderDate时间>12:00 PM，则从-1开始计数 - 如果Delivery Date为空，则计算与今天日期之间的差异

数据模型如下：

- OrderDate和DeliveryDate位于“OrderTable”中 - PublicHolidayDate位于“PublicHolidaysTable”中。

- Lokapedia

---

展示一下你已经尝试过的东西会很有用。 - Ben Thul

Answer 1

像SQL中的许多任务一样，这个问题可以用多种方法解决。

您可以使用在日期范围内带有BETWEEN运算符的COUNT聚合操作，从开始日期（OrderDate）到结束日期（DeliveryDate）给您提供周末和假日的聚合总数。

此功能可以与CTE（公共表达式）结合使用，以给您所需的最终结果集。

我编写了一个查询来演示如何解决问题的一种方式。我还准备了一些测试数据和结果，以说明查询的工作原理。

DECLARE @DateRangeBegin DATETIME = '2016-01-01'
      , @DateRangeEnd   DATETIME = '2016-07-01'

DECLARE @OrderTable TABLE 
(OrderNum INT, OrderDate DATETIME, DeliveryDate DATETIME)

INSERT INTO @OrderTable VALUES
  (1, '2016-02-12 09:30', '2016-03-01 13:00')
, (2, '2016-03-15 13:00', '2016-03-30 13:00')
, (3, '2016-03-22 14:00', NULL)
, (4, '2016-05-06 10:00', '2016-05-19 13:00')

DECLARE @PublicHolidaysTable TABLE 
(PublicHolidayDate DATETIME, Description NVARCHAR(255))

INSERT INTO @PublicHolidaysTable VALUES 
  ('2016-02-15', 'President''s Day')
, ('2016-03-17', 'St. Patrick''s Day')
, ('2016-03-25', 'Good Friday')
, ('2016-03-27', 'Easter Sunday')
, ('2016-05-05', 'Cinco de Mayo')

你可能已经考虑到的一些问题是，除非你公司在下周一观察这个假日，否则你不想计算周末和周末发生的假日。为了简单起见，在查询中排除任何周末日发生的假期。

你还需要将这种类型的查询限制在特定的日期范围内。

第一个CTE(cteAllDates)获取开始日期和结束日期范围内的所有日期。

第二个CTE(cteWeekendDates)从第一个CTE(cteAllDates)获取所有周末日期。

第三个CTE(ctePublicHolidays)从你的PublicHolidaysTable获取所有在工作日上发生的假期。

最后一个CTE(cteOrders)满足从下一天开始计算总天数和工作日数的要求(如果OrderDate在下午12:00之后)，并且如果DeliveryDate为空，则应使用今天的日期。

在CTE语句的末尾的select语句获取每个订单的总天数、周末天数、假期天数和工作日数。

;WITH cteAllDates AS (
SELECT 1 [DayID]
     , @DateRangeBegin [CalendarDate]
     , DATENAME(dw, @DateRangeBegin) [NameOfDay]
UNION ALL
SELECT cteAllDates.DayID + 1 [DayID]
     , DATEADD(dd, 1 ,cteAllDates.CalendarDate) [CalenderDate]
     , DATENAME(dw, DATEADD(d, 1 ,cteAllDates.CalendarDate)) [NameOfDay]
FROM cteAllDates
WHERE DATEADD(d,1,cteAllDates.CalendarDate) < @DateRangeEnd
)
, cteWeekendDates AS (
SELECT CalendarDate
FROM cteAllDates
WHERE NameOfDay IN ('Saturday','Sunday')
)
, ctePublicHolidays AS (
SELECT PublicHolidayDate
FROM @PublicHolidaysTable
WHERE DATENAME(dw, PublicHolidayDate) NOT IN ('Saturday', 'Sunday')
)
, cteOrders AS (
SELECT OrderNum
     , OrderDate
     , CASE WHEN DATEPART(hh, OrderDate) >= 12 THEN DATEADD(dd, 1, OrderDate) 
            ELSE OrderDate 
       END [AdjustedOrderDate]
     , CASE WHEN DeliveryDate IS NOT NULL THEN DeliveryDate 
            ELSE GETDATE() 
       END [DeliveryDate]
FROM @OrderTable
)
SELECT o.OrderNum
     , o.OrderDate
     , o.DeliveryDate
     , DATEDIFF(DAY, o.AdjustedOrderDate, o.DeliveryDate) [TotalDayCount]
     , (SELECT COUNT(*) FROM cteWeekendDates w 
        WHERE w.CalendarDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate) [WeekendDayCount]
     , (SELECT COUNT(*) FROM ctePublicHolidays h 
        WHERE h.PublicHolidayDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate) [HolidayCount]
     , DATEDIFF(DAY, o.AdjustedOrderDate, o.DeliveryDate)
       - (SELECT COUNT(*) FROM cteWeekendDays w 
          WHERE w.CalendarDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate)
       - (SELECT COUNT(*) FROM ctePublicHolidays h 
          WHERE h.PublicHolidayDate BETWEEN o.AdjustedOrderDate AND o.DeliveryDate) [WorkingDays]
FROM cteOrders o
WHERE o.OrderDate BETWEEN @DateRangeBegin AND @DateRangeEnd
OPTION (MaxRecursion 500)

使用测试数据，以上查询的结果如下：

我建议您简化上述内容，通过添加一个日历表来填充足够宽的日期范围。然后将一些CTE语句转换为视图。

我认为对您特别有价值的是一个视图，可以获取没有周末或节假日的工作日。然后，您只需获取两个日期之间的日期差异，并在同一范围内计算工作日数即可。

- Jon Dysinger