每小时总分钟数

SeanFlynn 发表于 Dev

肖恩·弗林

我在数据表中有两个字段-“ startTime”和“ endTime”。这两个字段表示用户在特定任务上花费的时间。这些是varchar字段。因此，假设我们的开始时间为“ 21:05:00”，结束时间为“ 22:09:00”。我需要代码来汇总分别在21小时和2200小时（即9分钟）中花费的总分钟数。因此，不仅是简单的分钟差异，而且是按小时细分。

最好的方法是什么？

到目前为止，我已经创建了一个表，该表将返回24小时内所有可能的小时数。这是一个示例：

Hour    startTime                endTime
0       2015-01-01 00:00:00.000  2015-01-01 01:00:00.000
1       2015-01-01 01:00:00.000  2015-01-01 02:00:00.000
2       2015-01-01 02:00:00.000  2015-01-01 03:00:00.000

我已经将startTime字段从varchar转换为dateteime，并将其称为sessionHour：

Convert(datetime, startTime) As sessionHour

另外，通过执行以下操作，我可以获得小时的开始时间：

DateAdd(Minute, 60 * (DateDiff(Minute, 0, startTime) / 60), 0)  As hourOf

除此之外，我不知道如何解析每小时的分钟数。

弗拉基米尔·巴拉诺夫（Vladimir Baranov）

你很亲密您只需要将数字表与数据结合起来。我会用CROSS APPLY的。这是带有最终解决方案的SQLFiddle。

样本数据

DECLARE @Durations TABLE (ID int IDENTITY(1,1), StartTime datetime, EndTime datetime);

INSERT INTO @Durations VALUES
('2015-01-01 21:05:00', '2015-01-01 22:09:00'),
('2015-01-01 01:05:00', '2015-01-01 01:20:00'),
('2015-01-01 11:05:00', '2015-01-01 13:09:00'),
('2015-01-01 15:05:00', '2015-01-01 17:50:00'),
('2015-01-01 16:30:00', '2015-01-01 17:20:00');

我datetime将从一开始就使用type，因为您已将varchar值转换为normal datetime。

我将使用数字表。它的行数应与数据中最长的持续时间（以小时为单位）一样多。它可能会超过24。通常，将这样的表放在数据库中以供其他报告很有用。

DECLARE @Numbers TABLE (Number int);
INSERT INTO @Numbers VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
-- Number of rows in this table should be more than the longest duration in hours

我需要一些参考时间来删除的微小部分datetime。只要没有分钟和秒，就可以是任何日期时间。

DECLARE @VarStart datetime;
SET @VarStart = '2000-01-01';

主要步骤-展开数据

SELECT *
FROM
    @Durations AS D
    CROSS APPLY
    (
        SELECT N.Number
        FROM @Numbers AS N
        WHERE N.Number <= DATEDIFF(hour, StartTime, EndTime)
    ) AS CA_Number
ORDER BY ID;

ID   StartTime              EndTime                Number
1    2015-01-01 21:05:00    2015-01-01 22:09:00    0
1    2015-01-01 21:05:00    2015-01-01 22:09:00    1
2    2015-01-01 01:05:00    2015-01-01 01:20:00    0
3    2015-01-01 11:05:00    2015-01-01 13:09:00    0
3    2015-01-01 11:05:00    2015-01-01 13:09:00    1
3    2015-01-01 11:05:00    2015-01-01 13:09:00    2
4    2015-01-01 15:05:00    2015-01-01 17:50:00    0
4    2015-01-01 15:05:00    2015-01-01 17:50:00    1
4    2015-01-01 15:05:00    2015-01-01 17:50:00    2
5    2015-01-01 16:30:00    2015-01-01 17:20:00    0
5    2015-01-01 16:30:00    2015-01-01 17:20:00    1

您可以看到，我们根据原始行的持续时间为每个原始行创建了几行。其余的是简单的算术运算。

每小时分钟

SELECT *
    ,DATEDIFF(minute, MaxStart, MinEnd) AS MinutesPerHour
FROM
    @Durations AS D
    CROSS APPLY
    (
        SELECT N.Number
        FROM @Numbers AS N
        WHERE N.Number <= DATEDIFF(hour, StartTime, EndTime)
    ) AS CA_Number
    CROSS APPLY
    (
        SELECT
            DATEADD(hour, CA_Number.Number, StartTime) AS HourStart
            ,DATEADD(hour, CA_Number.Number+1, StartTime) AS HourEnd
    ) AS CA_HourEnd
    CROSS APPLY
    (
        -- Truncate to 1 hour.
        SELECT
            DATEADD(hour, DATEDIFF(hour, @VarStart, HourStart), @VarStart) AS HourStartFinal
            ,DATEADD(hour, DATEDIFF(hour, @VarStart, HourEnd), @VarStart) AS HourEndFinal
    ) AS CA_HourEndFinal
    -- Intersect intervals [StartTime, EndTime] with [HourStartFinal, HourEndFinal]
    CROSS APPLY
    (
        SELECT
            CASE WHEN StartTime > HourStartFinal THEN StartTime ELSE HourStartFinal END AS MaxStart
            ,CASE WHEN EndTime < HourEndFinal THEN EndTime ELSE HourEndFinal END AS MinEnd
    ) AS CA_Intersect
ORDER BY ID;

在中CA_HourEnd，CA_HourEndFinal我使用来计算小时边界Number。然后相交两个间隔并计算每个相交的分钟数。这是结果集：

ID   StartTime              EndTime                Number    HourStart              HourEnd                HourStartFinal         HourEndFinal           MaxStart               MinEnd                 MinutesPerHour
1    2015-01-01 21:05:00    2015-01-01 22:09:00    0         2015-01-01 21:05:00    2015-01-01 22:05:00    2015-01-01 21:00:00    2015-01-01 22:00:00    2015-01-01 21:05:00    2015-01-01 22:00:00    55
1    2015-01-01 21:05:00    2015-01-01 22:09:00    1         2015-01-01 22:05:00    2015-01-01 23:05:00    2015-01-01 22:00:00    2015-01-01 23:00:00    2015-01-01 22:00:00    2015-01-01 22:09:00    9
2    2015-01-01 01:05:00    2015-01-01 01:20:00    0         2015-01-01 01:05:00    2015-01-01 02:05:00    2015-01-01 01:00:00    2015-01-01 02:00:00    2015-01-01 01:05:00    2015-01-01 01:20:00    15
3    2015-01-01 11:05:00    2015-01-01 13:09:00    0         2015-01-01 11:05:00    2015-01-01 12:05:00    2015-01-01 11:00:00    2015-01-01 12:00:00    2015-01-01 11:05:00    2015-01-01 12:00:00    55
3    2015-01-01 11:05:00    2015-01-01 13:09:00    1         2015-01-01 12:05:00    2015-01-01 13:05:00    2015-01-01 12:00:00    2015-01-01 13:00:00    2015-01-01 12:00:00    2015-01-01 13:00:00    60
3    2015-01-01 11:05:00    2015-01-01 13:09:00    2         2015-01-01 13:05:00    2015-01-01 14:05:00    2015-01-01 13:00:00    2015-01-01 14:00:00    2015-01-01 13:00:00    2015-01-01 13:09:00    9
4    2015-01-01 15:05:00    2015-01-01 17:50:00    0         2015-01-01 15:05:00    2015-01-01 16:05:00    2015-01-01 15:00:00    2015-01-01 16:00:00    2015-01-01 15:05:00    2015-01-01 16:00:00    55
4    2015-01-01 15:05:00    2015-01-01 17:50:00    1         2015-01-01 16:05:00    2015-01-01 17:05:00    2015-01-01 16:00:00    2015-01-01 17:00:00    2015-01-01 16:00:00    2015-01-01 17:00:00    60
4    2015-01-01 15:05:00    2015-01-01 17:50:00    2         2015-01-01 17:05:00    2015-01-01 18:05:00    2015-01-01 17:00:00    2015-01-01 18:00:00    2015-01-01 17:00:00    2015-01-01 17:50:00    50
5    2015-01-01 16:30:00    2015-01-01 17:20:00    0         2015-01-01 16:30:00    2015-01-01 17:30:00    2015-01-01 16:00:00    2015-01-01 17:00:00    2015-01-01 16:30:00    2015-01-01 17:00:00    30
5    2015-01-01 16:30:00    2015-01-01 17:20:00    1         2015-01-01 17:30:00    2015-01-01 18:30:00    2015-01-01 17:00:00    2015-01-01 18:00:00    2015-01-01 17:00:00    2015-01-01 17:20:00    20

最终查询

最后，我将分钟数汇总为一个小时：

SELECT
    HourStartFinal
    ,SUM(DATEDIFF(minute, MaxStart, MinEnd)) AS SumMinutesPerHour
FROM
    @Durations AS D
    CROSS APPLY
    (
        SELECT N.Number
        FROM @Numbers AS N
        WHERE N.Number <= DATEDIFF(hour, StartTime, EndTime)
    ) AS CA_Number
    CROSS APPLY
    (
        SELECT
            DATEADD(hour, CA_Number.Number, StartTime) AS HourStart
            ,DATEADD(hour, CA_Number.Number+1, StartTime) AS HourEnd
    ) AS CA_HourEnd
    CROSS APPLY
    (
        -- Truncate to 1 hour.
        SELECT
            DATEADD(hour, DATEDIFF(hour, @VarStart, HourStart), @VarStart) AS HourStartFinal
            ,DATEADD(hour, DATEDIFF(hour, @VarStart, HourEnd), @VarStart) AS HourEndFinal
    ) AS CA_HourEndFinal
    -- Intersect intervals [StartTime, EndTime] with [HourStartFinal, HourEndFinal]
    CROSS APPLY
    (
        SELECT
            CASE WHEN StartTime > HourStartFinal THEN StartTime ELSE HourStartFinal END AS MaxStart
            ,CASE WHEN EndTime < HourEndFinal THEN EndTime ELSE HourEndFinal END AS MinEnd
    ) AS CA_Intersect
GROUP BY HourStartFinal
ORDER BY HourStartFinal;

最终结果集

HourStartFinal             SumMinutesPerHour
2015-01-01 01:00:00.000    15
2015-01-01 11:00:00.000    55
2015-01-01 12:00:00.000    60
2015-01-01 13:00:00.000    9
2015-01-01 15:00:00.000    55
2015-01-01 16:00:00.000    90
2015-01-01 17:00:00.000    70
2015-01-01 21:00:00.000    55
2015-01-01 22:00:00.000    9

SQLFiddle

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。