0

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!

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Fabioe
  • 9
  • 2
  • 1
    Tag the database server you are using (sql server, mysql, etc) – Salman A Sep 17 '18 at 09:55
  • 1
    Difficult to understand, please give a clearer explaination! Maybe provide some code so we understand what you are doing. – Tito Sep 17 '18 at 11:08
  • 1
    Your example is quiet unclear. For 10:09 I do not see any 'A' action but there is a 'B' action. Similar with the 10:10 example. When does an event start and when does it end? What means "collaborating"? Please clarify your use case! – S-Man Sep 17 '18 at 11:22
  • Based on your description and the use of the word "collaboration", and the fact that in your result table the `start_time`is 10:02 (first time when both user 1 and 2 were working on A), the end time would be the time when they stop collaborating, which is 10:08 as that's when user 1 starts "B". Since this is obviously not the case, please explain better the conditions that constitute a "start" and "stop". Also as mentioned above your description of the desired output states "at 10:10 the user 1 make C" which is incorrect and adds to the confusion. – 404 Sep 17 '18 at 13:11

0 Answers0