基于T-SQL计算指定时间范围内的有效分钟数

Hey there, let's work through how to calculate the number of overlapping minutes between each row's time interval and a specified daily time frame using T-SQL. Here's the breakdown:

需求规则 #

For each row with StartDTS and EndDTS, we need to:

• Check if the row's time interval overlaps with our target time frame
• If there's an overlap, calculate the total minutes in that overlapping segment
• If no overlap exists, return 0 for that row

示例参考 #

Sample Scenario:
Row time intervals:

1. [StartDTS= 06:36:00] [EndDTS= 08:42:00]
2. [StartDTS= 09:37:00] [EndDTS= 13:42:00]
3. [StartDTS= 14:21:00] [EndDTS= 18:21:00]
   Target time frame: [Time_Frame_Start= 07:00:00] [Time_Frame_End= 15:30:00]

Calculated results:

1. Overlap from 07:00:00 to 08:42:00 → 102 minutes
2. Overlap from 09:37:00 to 13:42:00 → 245 minutes
3. Overlap from 14:21:00 to 15:30:00 → 69 minutes

测试数据准备 #

First, here's the temp table you provided with test data:

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

解决方案代码 #

Here's the T-SQL query that computes the overlapping minutes. The core idea is to find the start and end of the overlapping segment, then calculate the minutes between those two points:

-- Define your target daily time frame
DECLARE @TimeFrameStart TIME = '07:00:00';
DECLARE @TimeFrameEnd TIME = '15:30:00';

SELECT
    Indentifier AS RowIdentifier,
    StartDTS,
    EndDTS,
    -- Calculate utilized minutes
    CASE
        -- Check if there's any overlap between the row's interval and the daily time frame
        WHEN StartDTS < DATEADD(DAY, DATEDIFF(DAY, 0, EndDTS), @TimeFrameEnd)
             AND EndDTS > DATEADD(DAY, DATEDIFF(DAY, 0, StartDTS), @TimeFrameStart)
        THEN
            DATEDIFF(MINUTE,
                -- Overlap start: take the later of the row's start and the daily frame start
                IIF(StartDTS > DATEADD(DAY, DATEDIFF(DAY, 0, StartDTS), @TimeFrameStart),
                    StartDTS,
                    DATEADD(DAY, DATEDIFF(DAY, 0, StartDTS), @TimeFrameStart)
                ),
                -- Overlap end: take the earlier of the row's end and the daily frame end
                IIF(EndDTS < DATEADD(DAY, DATEDIFF(DAY, 0, EndDTS), @TimeFrameEnd),
                    EndDTS,
                    DATEADD(DAY, DATEDIFF(DAY, 0, EndDTS), @TimeFrameEnd)
                )
            )
        ELSE 0 -- No overlap, return 0
    END AS UtilizedMinutes
FROM #RowDTS
ORDER BY Indentifier;

代码说明 #

• DATEADD(DAY, DATEDIFF(DAY, 0, StartDTS), @TimeFrameStart): Combines the row's date with the time frame's start time to get the daily frame start for that row's date.
• IIF(): Used to pick the later start time and earlier end time of the overlapping segment.
• DATEDIFF(MINUTE, ...): Computes the total minutes in the overlapping interval. If there's no overlap, the CASE statement returns 0.

期望输出 #

Running this query will give you results matching your desired format, with the UtilizedMinutes column showing the overlapping minutes for each row. For example:

• Row 4739 overlaps from 13:46 to 15:30 → 104 minutes
• Row 4972 overlaps from 14:02 to 15:30 → 88 minutes

内容的提问来源于stack exchange，提问作者DayneBacon