我在数据表中有两个字段-“ 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
除此之外,我不知道如何解析每小时的分钟数。
你很亲密 您只需要将数字表与数据结合起来。我会用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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句