Q:

Finding the time difference between two specific SQL rows and populating a new table

I have a table, a shortened version is shown below. I need to find the time each ID goes offline. But, if an ID does not come online, it should be ignored, each ID can go offline multiple times, but needs to come online before it can go offline
|ID | Description | Time               |
|---|-------------|--------------------|
|1  |Offline      |'2017-09-07 12:53:02|
|---|-------------|--------------------|
|2  |Offline      |'2017-09-07 12:54:00|
|---|-------------|--------------------|
|2  |Online       |'2017-09-07 12:54:01|
|---|-------------|--------------------|
|3  |Offline      |'2017-09-07 12:54:02|
|---|-------------|--------------------|
|1  |Online       |'2017-09-07 12:55:21|
|---|-------------|--------------------|
|2  |Offline      |'2017-09-07 12:57:21|
|---|-------------|--------------------|
|2  |Online       |'2017-09-07 12:58:21|

This is the resulting table I need, the order doesn't matter(Time difference can be in seconds)
|ID |Time Difference |
|---|----------------|
|1  |141             |
|---|----------------|
|2  |1               |
|---|----------------|
|2  |60              |

A:

In your data, the online/offline states are interleaved.  This makes it easy to use lead():
select id,
       sum(datediff(second, time, next_t)
from (select t.*,
             lead(description) over (partition by id order by time) as next_d,
             lead(time) over (partition by id order by time) as next_t
      from t
     ) t
where description = 'Offline' and next_d = 'Online';

