代码之家 › 专栏 › 技术社区 › DayneBacon

定义时间范围的分钟利用率-T-SQL

ssms tsql sql-server

DayneBacon · 技术社区 · 6 年前

我需要计算活动分钟数,给定每行的StartDTS和ENDDT,在定义的时间范围内。

例如:

RowDTS:

[StartDTS=06:36:00][EndDTS=08:42:00]
[StartDTS=09:37:00][EndDTS=13:42:00]
[StartDTS=14:21:00][EndDTS=18:21:00]

时间框架:

[时间帧开始=07:00:00][时间帧结束=15:30:00]

如果StartDTS和EndDTS之间的任何部分时间都在时间_Frame_Start和时间_Frame_End内,则应计算定义时间范围内的分钟数。

CREATE TABLE #RowDTS (Indentifier varchar(255), StartDTS datetime2(7), EndDTS datetime2(7))
INSERT INTO #RowDTS
VALUES
('4318','2018-04-03 09:18:00.0000000','2018-04-03 10:20:00.0000000'),
('4397','2018-04-20 11:34:00.0000000','2018-04-20 12:27:00.0000000'),
('4459','2018-04-20 11:06:00.0000000','2018-04-20 11:54:00.0000000'),
('4739','2018-04-12 13:46:00.0000000','2018-04-12 17:34:00.0000000'),
('4845','2018-04-18 10:26:00.0000000','2018-04-18 15:18:00.0000000'),
('4933','2018-04-19 07:24:00.0000000','2018-04-19 09:51:00.0000000'),
('5063','2018-04-03 07:57:00.0000000','2018-04-03 11:00:00.0000000'),
('4855','2018-04-03 11:01:00.0000000','2018-04-03 11:51:00.0000000'),
('4858','2018-04-05 07:26:00.0000000','2018-04-05 11:12:00.0000000'),
('4972','2018-04-11 14:02:00.0000000','2018-04-11 16:36:00.0000000')
SELECT * FROM #RowDTS

这基本上就是我想要的结果:

+---------------+-----------------------------+-----------------------------+-----------------+
| RowIdentifier |          StartDTS           |           EndDTS            | UtilizedMinutes |
+---------------+-----------------------------+-----------------------------+-----------------+
|        431859 | 2018-04-03 09:18:00.0000000 | 2018-04-03 10:20:00.0000000 |                 |
|        439784 | 2018-04-20 11:34:00.0000000 | 2018-04-20 12:27:00.0000000 |                 |
|        445989 | 2018-04-20 11:06:00.0000000 | 2018-04-20 11:54:00.0000000 |                 |
|        473939 | 2018-04-12 13:46:00.0000000 | 2018-04-12 17:34:00.0000000 |                 |
|        484568 | 2018-04-18 10:26:00.0000000 | 2018-04-18 15:18:00.0000000 |                 |
|        493333 | 2018-04-19 07:24:00.0000000 | 2018-04-19 09:51:00.0000000 |                 |
|        506386 | 2018-04-03 07:57:00.0000000 | 2018-04-03 11:00:00.0000000 |                 |
|        485551 | 2018-04-03 11:01:00.0000000 | 2018-04-03 11:51:00.0000000 |                 |
|        485882 | 2018-04-05 07:26:00.0000000 | 2018-04-05 11:12:00.0000000 |                 |
|        497230 | 2018-04-11 14:02:00.0000000 | 2018-04-11 16:36:00.0000000 |                 |
+---------------+-----------------------------+-----------------------------+-----------------+

1 回复 | 直到 6 年前

Saeid 6 年前

我假设您的时间范围数据类型是time,并且所有StartDTS和EndDTS值都在同一天内。这个查询可以完成这项工作,但如果日期值可以分为不同的日期,则需要CTE来获得相同的结果。

DECLARE @TimeFrameStart TIME = '10:00:00';
DECLARE @TimeFrameEnd TIME = '15:30:00';

SELECT [Indentifier], 
    CONVERT(TIME, [StartDTS]) AS [StartDTS],
    CONVERT(TIME, [EndDTS]) AS [EndDTS],

    CASE 
    WHEN CONVERT(TIME, [StartDTS]) >= @TimeFrameStart AND CONVERT(TIME, [EndDTS]) <= @TimeFrameEnd
        THEN DATEDIFF(MINUTE, [StartDTS], [EndDTS])
    WHEN CONVERT(TIME, [StartDTS]) <= @TimeFrameStart AND CONVERT(TIME, [EndDTS]) >= @TimeFrameEnd
        THEN DATEDIFF(MINUTE, @TimeFrameStart, @TimeFrameEnd)
    WHEN CONVERT(TIME, [StartDTS]) <= @TimeFrameStart AND CONVERT(TIME, [EndDTS]) > @TimeFrameStart
        THEN DATEDIFF(MINUTE, @TimeFrameStart, CONVERT(TIME, [EndDTS]))
    WHEN CONVERT(TIME, [EndDTS]) >= @TimeFrameEnd AND CONVERT(TIME, [StartDTS]) < @TimeFrameEnd
        THEN DATEDIFF(MINUTE, CONVERT(TIME, [StartDTS]), @TimeFrameEnd)
    ELSE 0
    END AS [UtilizedMinutes]
FROM #RowDTS;