Ok, so I have a table on an MySQL server, it has the following columns:
Key, UserID, datetime and position
Position can be “start”, “login” and “end”.
I want to find out the difference between start, sign in and end for each login event.
Anyone have an idea of an SQL query I could use? I think it has something to do with a query within a query?
I postulate that I would first SELECT the start event and then for each “start” event it should look for the next first login event (after the time of the start but less than one hour after the start) then DATE_SUB() to find out the difference. It would also be good to filter out “next” events that happen after a future “start” event in case end didn’t happen.
I would be very grateful if anyone could save me doing this in Excel or Perl.