I want to know how long 2 users have collaborated even on different action. I have a table like this:
id | id_action | time
--------------------------
1 | A | 10:01
--------------------------
2 | A | 10:02
--------------------------
3 | C | 10:05
--------------------------
1 | B | 10:08
--------------------------
2 | B | 10:09
--------------------------
2 | A | 10:10
--------------------------
1 | C | 10:11
--------------------------
2 | C | 10:12
The fact that at 10:10 the user 2 make A and at 10:11 the user 1 make C must causes an interruption of this event, and then when the user 2 make C it started a new collaboration. The table that i want is this:
id1 |id2 | start_time | end_time
1 |2 | 10:02 | 10:08
1 |2 | 10:10 | null or 10:10 (i don't know yet)
This is my first question. Thank for your attention!