How to combine two rows and calculate time diff between two rows
750205May 9 2013 — edited May 12 2013Hi All,
I have a situation that I'm sure is quite common and it's really bothering me that I can't figure out how to do it or what to search for to find a relevant example/solution.
I have a table having the data as provided below.
ID Date Status
1, 2010-06-10 19:45, Connect
1, 2010-06-10 19:47, Disconnect
1, 2010-07-10 20:13, Connect
1,2010-07-10 20:13, Disconnect
2,2010-07-10 20:13, Connect
2,2010-08-10 20:13, DisConnect
Let's say that some rows have an Status= 'Connect' and others have an Status= 'stop'. What I want to do is to somehow couple each "Connect" with next each "Disconnect" and find the time difference between the two (and then sum the durations per each name, but that's not where the problem lies). Each "Connect" event should have a corresponding "Disconnect" event occuring at some stage later than the "Connect" event. If there is any ID with only Connect event without Disconnecct we can exclude them.
Please suggest me on this.
Thanks in advance