Two data time difference calculation in SQL same column

Problem: From the request to the demand through, the middle will experience an indeterminate number of "dismiss--and revoke dismiss" operation, all the operation time is in the same column. Request: The time required from the proposed to the passing through (not including the time spent by "dismiss-dismiss")

PS: "Dismiss and dismiss" action appears in pairs, and in chronological order.

1. Build a table

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 (OdateDATETIME, OperationVARCHAR( -), IDINT);INSERT  into#logsVALUES('2018-08-09 09:00:00','Submit Demand',1);INSERT  into#logsVALUES('2018-08-09 10:00:00','dismissed',1);INSERT  into#logsVALUES('2018-08-09 11:30:00','Revocation of Refusal',1);INSERT  into#logsVALUES('2018-08-09 12:00:00','dismissed',1);INSERT  into#logsVALUES('2018-08-09 13:10:00','Revocation of Refusal',1);INSERT  into#logsVALUES('2018-08-09 13:50:00','dismissed',1);INSERT  into#logsVALUES('2018-08-09 19:00:00','Revocation of Refusal',1);INSERT  into#logsVALUES('2018-08-09 22:00:00','through',1);INSERT  into#logsVALUES('2018-08-10 09:00:00','Submit Demand',2);INSERT  into#logsVALUES('2018-08-10 10:00:00','dismissed',2);INSERT  into#logsVALUES('2018-08-11 11:30:00','Revocation of Refusal',2);INSERT  into#logsVALUES('2018-08-11 12:00:00','dismissed',2);INSERT  into#logsVALUES('2018-08-11 13:00:00','Revocation of Refusal',2);INSERT  into#logsVALUES('2018-08-11 22:00:00','through',2);

Results

2. Group by ID, sort the time by number

-- sort and number the time SELECT  Over  by ORDER  by   intofrom #logs

Results

3. Append the next action execution time to the end of the bank

-- new column: Time of next Operation SELECT  as adate, (    SELECTfromWHERE rownum=t.rownum+1   and ID=t.id) as bdate  into  from #logs2 t

Results

4. Calculation results

-- calculation requirements presented to pass time-consuming (reject dismiss to dismiss dismiss time-consuming) SELECT ID,SUM(DATEDIFF from #logs3WHERE operation<>'  dismiss 'GROUP by ID '

Two data time difference calculation in SQL same column