another way of doing, if you really want to do it in SQL
-select '(''' + ID + ''', ''' + convert(varchar(8), START_TIME) + ''', ''' + convert(varchar(8), END_TIME) + '''),' FROM T_KL_TEST3
/*
CREATE TABLE dbo.T_KL_TEST3 (ID VARCHAR(10), START_TIME TIME, END_TIME TIME)
INSERT T_KL_TEST3 VALUES
('A', '08:00:00', '08:20:00'),
('B', '08:15:00', '08:23:00'),
('C', '08:21:00', '08:26:00'),
('D', '08:30:00', '08:32:00'),
('E', '08:33:00', '08:52:00'),
('F', '08:39:00', '08:42:00'),
('G', '08:45:00', '08:49:00')
*/
WITH CTE1 AS (
SELECT * FROM T_KL_TEST3 WHERE ID NOT IN (
select B.ID from T_KL_TEST3 A
LEFT JOIN T_KL_TEST3 B
ON A.START_TIME < B.START_TIME AND A.END_TIME > B.END_TIME
WHERE B.ID IS NOT NULL
)),
CTE AS (
SELECT START_TIME, END_TIME FROM CTE1
UNION ALL
SELECT CTE.START_TIME, T.END_TIME FROM CTE1 T
JOIN CTE
ON T.START_TIME BETWEEN CTE.START_TIME AND CTE.END_TIME
AND T.END_TIME > CTE.END_TIME
)
SELECT ROW_NUMBER() OVER (ORDER BY MIN(START_TIME)) RN,
MIN(START_TIME) as START_TIME, END_TIME
FROM (
SELECT START_TIME, MAX(END_TIME) as END_TIME
FROM CTE
GROUP BY START_TIME
) A
GROUP BY END_TIME
ORDER BY START_TIME, END_TIME
※ 引述《howard7821 (howard7821)》之銘言：
: 標題可能不是很清楚我想表達的
: 目前有個需求是想要計算業務的總通話時長
: 但一個業務同時間可能會與很多客戶對話
: 舉例來說
: 客戶 開始對話時間 結束對話時間
: A 08:00:00 08:20:00
: B 08:15:00 08:23:00
: C 08:21:00 08:26:00
: D 08:30:00 08:32:00
: E 08:33:00 08:52:00
: F 08:39:00 08:42:00
: G 08:45:00 08:49:00
: 以上就是這業務與客戶的對話時間表
: 然後想整理成以下資訊
: 1 08:00:00 08:26:00
: 2 08:30:00 08:32:00
: 3 08:33:00 08:52:00
: 也就是對話時間有連貫就合成一筆資料
: 求大神幫小弟解答一下 感謝