Complete rewrite based on new information. How I approached this was to start with an inner-most query to get all records we care about based exclusively on HomeID = 111 and make sure they came back pre-sorted by the sequence ID (have index on HomeID, Sequence). As we all know, a phone call starts by picking up the phone -- eventID = 12, getting dial tone -- eventid = 22, dialing out, and someone answering, until the phone is back on the hook -- eventid = 30). If its a hangup (eventid=13), we want to ignore it.
I don't know why you are looking at the sequence # PRIOR to the current call, don't know if it really has any bearing. It looks like you are just trying to get completed calls and how long the duration. That said, I would remove the portion of the LEFT JOIN Phone_Event and the corresponding WHERE clause. It may have been there while you were just trying to figure this out.
Anyhow, back to the logic. The inner most guarantees the call sequences in order. You won't have two calls simultaneous. So by getting them in order first, I then join to the SQLVars (which creates inline variable @NextCall for the query). The purpose for this is to identify every time a new call is about to begin (EventID = 12). If so, take whatever the sequence number is, and save it. This will remain the same until the next call, so all the other "event IDs" will have the same "starting sequence ID". In addition, I'm looking for the other events... an event = 22 based on the starting sequence +1 and setting it as a flag. Then, the max time based on the start of the call (only set when eventid = 12), and end of the call (eventid = 30), and finally a flag based on your check for a hang up (eventid = 13) ie: don't consider the call if it was a hangup and no connection through.
By doing a group by, I've in essence, rolled-up each call to its own line... grouped by the home ID, and the sequence number used to initiate the actual phone call. Once THAT is done, I can then query the data and compute the call duration since the start/end time are on the same row, no self-self-self joins involved.
Finally, the where clause... Kick out any phone calls that HAD a HANG UP. Again, I don't know if you still need the element of what the starting call's time was of the last ending event.
SELECT
PreGroupedCalls.*,
timediff( PreGroupedCalls.CallEndTime, PreGroupedCalls.CallStartTime ) CallDuration
from
( SELECT
Calls.HomeID,
@NextCall := @NextCall + if( Calls.EventID = 12, Calls.Sequence, @NextCall ) as NextNewCall,
MAX( if( Calls.EventID = 12, Calls.Stamp, 0 )) as CallStartTime,
MAX( if( Calls.EventID = 30, Calls.Stamp, 0 )) as CallEndTime,
MAX( if( Calls.EventID = 22 and Calls.Sequence = @NewCallFirstSeq +1, 1, 0 )) as HadDTMFEntry,
MAX( if( Calls.EventID = 13 and Calls.Sequence = @NewCallFirstSeq +1, 1, 0 )) as WasAHangUp
from
( select pe.HomeId,
pe.Sequence,
pe.EventID,
pe.Stamp
from
Phone_Events pe
where
pe.HomeID = 111
order by
pe.Sequence ) Calls,
( select @NextCall := 0 ) SQLVars
group by
Calls.HomeID,
NextNewCall ) PreGroupedCalls
LEFT JOIN Phone_Event PriorCallEvent
ON PreGroupCalls.NextNewCall = PriorCallEvent.Sequence -1
where
PreGroupedCalls.WasHangUp = 0
AND ( PriorCallEvent.Sequence IS NULL
OR abs(timediff( PriorCallEvent.Stamp, PreGroupedCalls.CallStartTime )) > 10 )
COMMENT FROM FEEDBACK / ERROR reported
To try and fix the DOUBLE error, you obviously will need to make a slight change in the SQLVars select.. try the following
( select @NextCall := CAST( 0 as INT ) ) SQLVars
Now, what the IF() is doing... Lets take a look.
@NextCall + if(Calls.EventID = 12,Calls.Sequence, @NextCall)
means take a look at the Event ID. If it is a 12 (ie: off-hook), grab whatever the sequence number is for that entry. This will become the new "Starting Sequence" of another call. If not, just keep whatever the last value set was, as its a continuation of a call in progress. Now, lets look at some simulated data to help better illustrate all the columns
Original data Values that will ultimately be built into...
HomeID Sequence EventID Stamp @NextCall
111 1 12 8:00:00 1 beginning of a new call
111 2 22 8:00:01 1 not a new "12" event, keep last value
111 3 30 8:05:00 1 call ended, phone back on hook
111 4 12 8:09:00 4 new call, use the sequence of THIS entry
111 5 22 8:09:01 4 same call
111 6 13 8:09:15 4 same call, but a hang up
111 7 30 8:09:16 4 same call, phone back on hook
111 8 12 8:15:30 8 new call, get sequence ID
111 9 22 8:15:31 8 same call...
111 10 30 8:37:15 8 same call ending...
Now, the query SHOULD create something like this
HomeID NextNewCall CallStartTime CallEndTime HadDTMFEntry WasAHangUp
111 1 8:00:00 8:05:00 1 0
111 4 8:09:00 8:09:16 1 1
111 8 8:15:30 8:37:15 1 0
As you can see, the @NextCall keeps all the sequential entries for a given call "Grouped" together so you don't have to just use greater than span information or less than... It is always going to follow a certain path of "events", so whatever is the one that started the call is the basis for the rest of the events until the next call is started, then THAT sequence is grabbed for THAT group call.
Yup, its a lot to grasp.. but hopefully now more digestible for you :)
