原贴:点击打开链接
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
*/