统计两表时间段重复部分

原贴：点击打开链接

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
*/