sql - 如何计算SQL中两个日期之间的公共(public)假期的工作天数和小时数

Question

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

我想达到什么目的？

我想计算两个时间戳字段之间的以下两种类型的总天数。

1. 工作天数(不包括周末和公共(public)假期)
2. 总天数(包括周末和公共(public)假期)

计算条件

• 如果 OrderDate 时间 <= 12:00 PM 则从 0 开始计数

• 如果 OrderDate 时间 > 12:00 PM 则从 -1 开始计数

• 如果 Delivery Date 为 NULL，则计数与今天不同

数据模型

• OrderDate 和 DeliveryDate 位于“OrderTable”
• PublicHolidayDate 位于“PublicHolidaysTable”

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 为 null 则应使用今天的日期的要求。

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 语句并将它们转化为 View 。

我认为对您来说特别有值(value)的是让您在没有周末或节假日的情况下工作的 View 。然后你可以简单地获取两个日期之间的日期差异并计算同一范围内的工作日。