CODEWITHSUNDEEP
sql-serversql-server-2008-r2
Rajkishan Swami
Rajkishan Swami

Reputation: 3759

Calculating Time Difference Between pairs of rows

I have database records like this:

Connected                 UpdatedTime
1                         2015-02-05 13:13:23.420
0                         2015-02-05 23:40:50.360
1                         2015-02-05 23:40:50.357
0                         2015-02-05 23:34:41.250

1 is when I connect external device to pc and 0 is when I disconnect it. I want to find how many seconds usb device was connected and how many seconds it was disconnected.

My Question:

Can I do that using MSSQL Query?

Upvotes: 2

Views: 449

Answers (1)

Tanner
Tanner

Reputation: 22733

I've tweaked the values to make the result more meaningful (and verifiable) than the thousands of seconds in your sample data. I've used a self-joining recursive cte to perform the DATEDIFF calculations, like so:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE USBConnections
    ([Connected] int, [UpdatedTime] datetime)
;

INSERT INTO USBConnections
    ([Connected], [UpdatedTime])
VALUES
    (1, '2015-02-05 13:00:00'),
    (0, '2015-02-05 13:00:50'),
    (1, '2015-02-05 14:00:50'),
    (0, '2015-02-05 14:00:55')
;

Query 1:

Note the recursive cte joins to itself based on the row number being -1 less than the current row and the status having switched between 1 and 0.

;WITH cte AS
(
SELECT [Connected], [UpdatedTime], 
       ROW_NUMBER() OVER ( ORDER BY [UpdatedTime]) rownum
FROM USBConnections
)
SELECT SUM(DATEDIFF(SECOND, cte1.[UpdatedTime], cte2.[UpdatedTime]) ) ConnectedTime
FROM    cte cte1
INNER JOIN cte cte2 ON cte1.rownum = cte2.rownum - 1 
      AND (cte1.Connected = 1 AND cte2.Connected = 0)

Results:

| CONNECTEDTIME |
|---------------|
|            55 |

Reference:

Recursive Queries Using Common Table Expressions (CTE)

Upvotes: 1

Related Questions