SQL Server 2008 - 考虑自定义假期和周末，计算两个日期之间的业务分钟数

Question

使用 SQL Server 2008，我需要对两个日期时间字段之间的营业分钟求和，同时考虑非工作时间和周末/公司假期。如果可能的话，我想合并一个日历，这样我就可以轻松编辑任何假期。

例如

OpenCall              CloseCall 
05/08/2013 14:00:00   06/08/2013 09:30:00             

上面的结果，需要返回:240——(4小时)工作时间是:08:30-17:00。

如果调用在周五打开并在周二关闭，那么它应该只计算周五、周一和周二(即不是周末)的工作时间之间的分钟数。

我是 SQL/T-SQL 的新手，所以请清楚地解释任何代码/变量 - 如果您能找到一个简洁的解决方案!

提前致谢!

Answer 1

首先，这是我使用的结构，我认为不需要太多调整就可以适应您的结构。

(请注意，我会在您的日历表中推荐更多字段，但 IsWorkingDay 是此示例唯一需要的字段)

SET DATEFIRST 1;
CREATE TABLE dbo.Calendar
(       [Date]          DATE NOT NULL,
        IsWorkingDay    BIT NOT NULL
    CONSTRAINT PK_Calendar_Date PRIMARY KEY ([Date])
);

-- INSERT DATES IN 2013 (NOT DOING A FULL TABLE AS IT'S JUST AN EXAMPLE)
INSERT dbo.Calendar ([Date], IsWorkingDay)
SELECT  [Date] = DATEADD(DAY, Number, '20130101'), 1
FROM    Master..spt_values
WHERE   Type = 'P'
AND     Number < 365;

-- UPDATE NON WORKING DAYS
UPDATE  dbo.Calendar
SET     IsWorkingDay = 0
WHERE   DATEPART(WEEKDAY, [Date]) IN (6, 7)
OR      [Date] IN ('20130101', '20130329', '20130401', '20130506', '20130527', '20130826', '20131225', '20131226');

-- CREATE SAMPLE DATA
CREATE TABLE T (OpenCall DATETIME NOT NULL, CloseCall DATETIME NOT NULL);
INSERT T (OpenCall, CloseCall)
VALUES 
    ('20130805 14:00:00', '20130806 09:30:00'),
    ('20130823 16:00:00', '20130828 10:30:00'); -- CROSS BANK HOLIDAY AND WEEKEND

第一步是获取两个日期之间的所有天数。您可以通过加入日历表来完成此操作，其中日历表中的日期介于开始和结束日期时间之间:

SELECT  T.OpenCall,
        T.CloseCall,
        Calendar.[Date],
        StartTime = CASE WHEN CAST(T.OpenCall AS DATE) = Calendar.[Date] THEN CAST(T.OpenCall AS TIME) ELSE CAST('08:30' AS TIME) END,
        EndTime = CASE WHEN CAST(T.CloseCall AS DATE) = Calendar.[Date] THEN CAST(T.CloseCall AS TIME) ELSE CAST('17:00' AS TIME) END
FROM    T
        INNER JOIN Calendar
            ON Calendar.Date >= CAST(T.OpenCall AS DATE)
            AND Calendar.Date <= CAST(T.CloseCall AS DATE)
            AND Calendar.IsWorkingDay = 1;

对于示例数据，这将给出

+---------------------+---------------------+------------+----------+----------+
| OpenCall            | CloseCall           |   Date     |StartTime | EndTime  |
|---------------------+---------------------+------------+----------+----------|
| 2013-08-05 14:00:00 | 2013-08-06 09:30:00 | 2013-08-05 | 14:00:00 | 17:00:00 |
| 2013-08-05 14:00:00 | 2013-08-06 09:30:00 | 2013-08-06 | 08:30:00 | 09:30:00 |
|---------------------+---------------------+------------+----------+----------|
| 2013-08-23 16:00:00 | 2013-08-28 10:30:00 | 2013-08-23 | 16:00:00 | 17:00:00 |
| 2013-08-23 16:00:00 | 2013-08-28 10:30:00 | 2013-08-27 | 08:30:00 | 17:00:00 |
| 2013-08-23 16:00:00 | 2013-08-28 10:30:00 | 2013-08-28 | 08:30:00 | 09:30:00 |
+---------------------+---------------------+------------+----------+----------+

如您所见，在第一天它使用源数据中的打开时间，在每个范围的最后一天它使用源数据中的关闭时间，对于所有其他开始/结束时间它使用硬编码营业时间(在本例中为上午 9 点至下午 5.30)。

最后一步只是总结每个范围的开始时间和结束时间之间的差异:

WITH Data AS
(   SELECT  T.OpenCall,
            T.CloseCall,
            StartTime = CASE WHEN CAST(T.OpenCall AS DATE) = Calendar.[Date] THEN CAST(T.OpenCall AS TIME) ELSE CAST('08:30' AS TIME) END,
            EndTime = CASE WHEN CAST(T.CloseCall AS DATE) = Calendar.[Date] THEN CAST(T.CloseCall AS TIME) ELSE CAST('17:00' AS TIME) END
    FROM    T
            INNER JOIN Calendar
                ON Calendar.Date >= CAST(T.OpenCall AS DATE)
                AND Calendar.Date <= CAST(T.CloseCall AS DATE)
                AND Calendar.IsWorkingDay = 1
)
SELECT  OpenCall,
        CloseCall,
        BusinessMinutes = SUM(DATEDIFF(MINUTE, StartTime, EndTime))
FROM    Data
GROUP BY OpenCall, CloseCall;

给出最终结果:

+---------------------+---------------------+--------------------+
| OpenCall            | CloseCall           |   BusinessMinutes  |
|---------------------+---------------------+--------------------+
| 2013-08-05 14:00:00 | 2013-08-06 09:30:00 |        240         |
| 2013-08-23 16:00:00 | 2013-08-28 10:30:00 |        690         |
+---------------------+---------------------+--------------------+

Example on SQL Fiddle