Sql同一列中兩條資料時間差計算

標籤：arc   partition   number   drop   info   div   select   weight   varchar   

問題：從提出需求到需求通過，中間會經曆不確定次數的“駁回->撤銷駁回”的操作，所有的操作時間都位於同一列。求：需求從提出到通過經曆的時間（不包含“駁回-撤銷駁回”所消耗的時間）

PS： “駁回->撤銷駁回”操作成對出現；且按時間順序排列。

 

1. 建表

IF OBJECT_ID(‘tempdb..#logs‘,‘U‘)is NOT NULL DROP TABLE #logsIF OBJECT_ID(‘tempdb..#logs2‘,‘U‘)is NOT NULL DROP TABLE #logs2IF OBJECT_ID(‘tempdb..#logs3‘,‘U‘)is NOT NULL DROP TABLE #logs3CREATE TABLE #logs(    ODate DATETIME    ,Operation VARCHAR(20)    ,ID INT);INSERT INTO #logs VALUES(‘2018-08-09 09:00:00‘,‘提交需求‘,1);INSERT INTO #logs VALUES(‘2018-08-09 10:00:00‘,‘駁回‘,1);INSERT INTO #logs VALUES(‘2018-08-09 11:30:00‘,‘撤銷駁回‘,1);INSERT INTO #logs VALUES(‘2018-08-09 12:00:00‘,‘駁回‘,1);INSERT INTO #logs VALUES(‘2018-08-09 13:10:00‘,‘撤銷駁回‘,1);INSERT INTO #logs VALUES(‘2018-08-09 13:50:00‘,‘駁回‘,1);INSERT INTO #logs VALUES(‘2018-08-09 19:00:00‘,‘撤銷駁回‘,1);INSERT INTO #logs VALUES(‘2018-08-09 22:00:00‘,‘通過‘,1);INSERT INTO #logs VALUES(‘2018-08-10 09:00:00‘,‘提交需求‘,2);INSERT INTO #logs VALUES(‘2018-08-10 10:00:00‘,‘駁回‘,2);INSERT INTO #logs VALUES(‘2018-08-11 11:30:00‘,‘撤銷駁回‘,2);INSERT INTO #logs VALUES(‘2018-08-11 12:00:00‘,‘駁回‘,2);INSERT INTO #logs VALUES(‘2018-08-11 13:00:00‘,‘撤銷駁回‘,2);INSERT INTO #logs VALUES(‘2018-08-11 22:00:00‘,‘通過‘,2);

結果

 

 

2. 按ID分組，對時間進行排序編號

--對時間進行排序並編號SELECT ID,ODate,Operation,ROW_NUMBER() OVER (PARTITION BY id ORDER BY ODate)RowNum INTO #logs2 FROM #logs

結果

 

3. 將下一操作執行時間追加到本行末尾

--新增列：下一步操作的時間SELECT ID,Operation,ODate AS ADate ,(    SELECT ODate FROM #logs2 WHERE rownum=t.rownum+1 and ID=t.id)AS BDateINTO #logs3 FROM #logs2 t

 結果

 

4. 計算結果

--計算需求提出到通過耗時（剔除駁回到撤銷駁回耗時）SELECT ID,SUM(DATEDIFF(MI,ADate,Bdate)) CostTime FROM #logs3WHERE Operation<>‘駁回‘GROUP BY ID

 

 

Sql同一列中兩條資料時間差計算