?
有如下表：
-----------------------------------------------------------------------------------------------------------------
设备 开始时间 结束时间 总时间（分钟）
A 2015-08-01 17:06:49 2015-08-02 03:41:32 635
B 2015-08-01 20:54:42 2015-08-03 23:59:59 3605
----------------------------------------------------------------------------------------------------------------
需求：
1、将每个设备的时间段循环得出每天总时间
2、1天的时间计算结果必须是1440
3、不能用游标方式处理
需求结果集表如下：
-----------------------------------------------------------------------------------------------------------------
设备 开始时间 结束时间 总时间（分钟）
A 2015-08-01 17:06:49 2015-08-01 23:59:59 413
A 2015-08-02 00:00:00 2015-08-02 03:41:32 222
B 2015-08-01 20:54:42 2015-08-01 23:59:59 185
B 2015-08-02 00:00:00 2015-08-02 23:59:59 1440
B 2015-08-03 00:00:00 2015-08-03 23:59:59 1440
----------------------------------------------------------------------------------------------------------------------
?
该问题已有网友用递归实现，如果不用递归，我们该怎么处理呢？下面，我说一下我的实现思路：
?
1.求出每个设备开始时间和结束时间相差的天数
?
2.生成分解后的新时间
?
3.求出分解后每个设备每天的时间差（分钟），如果是一整天，则计为1440；
?
如果是最后一天，则用总时间减去前面的时间总和。
?
SQL实现
/*测试数据*/
WITH x0
AS ( SELECT 'A' AS sb ,
CONVERT(DATETIME, '2015-08-01 17:06:49') AS date_begin ,
CONVERT(DATETIME, '2015-08-02 03:41:32') AS date_end ,
635 AS total_time
UNION ALL
SELECT 'B' AS sb ,
CONVERT(DATETIME, '2015-08-01 20:54:42') AS date_begin ,
CONVERT(DATETIME, '2015-08-03 23:50:52') AS date_end ,
3056 AS total_time
UNION ALL
SELECT 'C' AS sb ,
CONVERT(DATETIME, '2015-08-04 12:40:20') AS date_begin ,
CONVERT(DATETIME, '2015-08-05 23:59:59') AS date_end ,
2119 AS total_time
),/*计算两个时间点之间相差的天数*/
x1
AS ( SELECT sb ,
date_begin ,
date_end ,
total_time ,
DATEDIFF(day, date_begin, date_end) AS cacl_day--开始时间和结束时间相差的天数
FROM x0
),/*将隔天的时间分解*/
x2
AS ( SELECT sb ,
CASE WHEN msv.number = 0 THEN date_begin
ELSE CONVERT(VARCHAR(10), DATEADD(DAY, msv.number,
date_begin), 120)
END AS date_begin ,
CASE WHEN msv.number = x.cacl_day THEN date_end
ELSE CONVERT(VARCHAR(10), DATEADD(DAY, msv.number,
date_begin), 120)
+ ' 23:59:59'
END AS date_end ,
total_time ,
CASE WHEN msv.number = x.cacl_day THEN 1
ELSE 0
END AS flag--如果是最后一天，标识为1，否则为0
FROM x1 x ,
master..spt_values msv
WHERE msv.type = 'P'
AND msv.number <= x.cacl_day
),
x3
AS ( SELECT sb ,
date_begin ,
date_end ,
total_time ,
CASE WHEN CONVERT(CHAR(8), date_begin, 108) = '00:00:00'
AND CONVERT(CHAR(8), date_end, 108) = '23:59:59'
THEN 1440
ELSE DATEDIFF(MINUTE, date_begin, date_end)
END AS cacl_time ,--如果是一整天，那么是1440分钟，否则，2个时间相减
flag ,
ROW_NUMBER() OVER ( PARTITION BY sb ORDER BY date_end ) AS rn--行号，用于后面统计各天的剩余分钟
FROM x2
)
SELECT sb ,
date_begin ,
date_end ,
CASE WHEN flag = 1 THEN total_time - ( SELECT SUM(cacl_time)
FROM x3 x
WHERE x.sb = x3.sb
AND x.rn < x3.rn
)
ELSE cacl_time--最后一天的耗时是总时间减去前面的时间总和
END AS cacl_time
FROM x3
?
我自己添加了一些测试数据，先看下原表的数据
?
?
感兴趣的朋友，可以对比一下这2种方法实现的异同。
