作者ken218 (ken218)
標題Re: [SQL ] 取得一段時間長度並去除重複
時間Wed Dec 18 01:17:40 2019
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
: 也就是對話時間有連貫就合成一筆資料
: 求大神幫小弟解答一下 感謝
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 3.8.111.149 (英國)
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Database/M.1576603062.A.53B.html
1F：推 howard7821: 感謝解答！ 12/18 08:07
2F：→ ken218: 實作完要 po 的時候才發現 cutekid 大也有解。我的做法雷 12/18 17:26
3F：→ ken218: 同，所以其實參考價值不高。我是做完時間有延續的部分才發 12/18 17:26
4F：→ ken218: 現時間互包的問題。不過這種如果非得要在 SQL 裡實作，可 12/18 17:26
5F：→ ken218: 能用多幾個 table 處理會比較有效率 12/18 17:26