22,210
社区成员
发帖
与我相关
我的任务
分享
设备 开始时间 结束时间
1 3:00 5:00
1 6:00 7:00
已知B表(工作时间定义表)数据如下:
ID 开始时间 结束时间
1 00:00 4:00
2 04:30 6:30
3 09:00 12:20
现在想要统计A表中的有效工作时间,注意,A表中的工作时间必须在B表定义的区间内才算有效。例如,A表第一条记录的工作时间区间是:3:00~5:00,根据B表的工作时间定义,它的有效时间分拆为2段:3:00~4:00、4:00~4:30,合计为1.5小时。
同理,第2条记录有效时间应该为:6:00~6:30,合计为0.5小时。合计设备为1的总的有效工作时间为:2小时。
上面只是部分数据,实际上A、B两表不止这些数据。
恳请高手帮忙解决,非常感谢!
USE tempdb
GO
IF OBJECT_ID('dbo.a') IS NOT NULL DROP TABLE dbo.a
IF OBJECT_ID('dbo.b') IS NOT NULL DROP TABLE dbo.b
CREATE TABLE a(deviceId INT ,beginTime TIME,endTime TIME)
CREATE TABLE b(Id INT PRIMARY KEY,beginTime TIME,endTime TIME)
--
SET NOCOUNT ON
INSERT INTO a VALUES (1,'3:00','5:00')
INSERT INTO a VALUES (1,'6:00','7:00')
--
INSERT INTO b VALUES (1,'0:00','4:00')
INSERT INTO b VALUES (2,'4:30','6:30')
INSERT INTO b VALUES (3,'9:00','12:20')
;WITH cte AS (
SELECT a.deviceId,SUM(DATEDIFF(n,a.beginTime,a.endTime)) AS totalMinutes
FROM a
GROUP BY a.deviceId
)
SELECT t.deviceId
,(SELECT totalMinutes FROM cte WHERE cte.deviceId=t.deviceId) AS [总(分钟)]
,SUM(t.diffMinutes) AS [有效(分钟)]
,(SELECT totalMinutes FROM cte WHERE cte.deviceId=t.deviceId)-SUM(t.diffMinutes) AS [无效(分钟)]
FROM (
SELECT a.deviceId,bb.diffMinutes
FROM a CROSS APPLY (SELECT
DATEDIFF(n,CASE WHEN a.beginTime>b.beginTime THEN a.beginTime ELSE b.beginTime END
,CASE WHEN a.endTime<b.endTime THEN a.endTime ELSE b.endTime END) AS diffMinutes
FROM b WHERE a.beginTime BETWEEN b.beginTime AND b.endTime
OR a.endTime BETWEEN b.beginTime AND b.endTime) bb
) AS t
GROUP BY t.deviceId
/*
deviceId 总(分钟) 有效(分钟) 无效(分钟)
----------- ----------- ----------- -----------
1 180 120 60
*/
USE tempdb
GO
IF OBJECT_ID('dbo.a') IS NOT NULL DROP TABLE dbo.a
IF OBJECT_ID('dbo.b') IS NOT NULL DROP TABLE dbo.b
CREATE TABLE a(deviceId INT ,beginTime TIME,endTime TIME)
CREATE TABLE b(Id INT PRIMARY KEY,beginTime TIME,endTime TIME)
--
SET NOCOUNT ON
INSERT INTO a VALUES (1,'3:00','5:00')
INSERT INTO a VALUES (1,'6:00','7:00')
--
INSERT INTO b VALUES (1,'0:00','4:00')
INSERT INTO b VALUES (2,'4:30','6:30')
INSERT INTO b VALUES (3,'9:00','12:20')
SELECT t.deviceId
,SUM(t.diffMinutes) AS totalMinutes
FROM (
SELECT a.deviceId,bb.diffMinutes
FROM a CROSS APPLY (SELECT
DATEDIFF(n,CASE WHEN a.beginTime>b.beginTime THEN a.beginTime ELSE b.beginTime END
,CASE WHEN a.endTime<b.endTime THEN a.endTime ELSE b.endTime END) AS diffMinutes
FROM b WHERE a.beginTime BETWEEN b.beginTime AND b.endTime
OR a.endTime BETWEEN b.beginTime AND b.endTime) bb
) AS t
GROUP BY t.deviceId
/*
deviceId totalMinutes
----------- ------------
1 120
*/