CODEWITHSUNDEEP
sqldb2
Steve.G
Steve.G

Reputation: 47

DB2 SQL Query to Identify what event occured prior to a particular event in a sequence

I have a table from our IVR that contains a unique call id, sequence number, event code, and event description. I would like to write a query that let's me know what was the event prior to a particular event.

Upvotes: 1

Views: 189

Answers (3)

Fred Sobotka
Fred Sobotka

Reputation: 5332

Depending on what indexes exist on the table, a straightforward inner join may receive a better-performing access plan from the query optimizer.

SELECT n.call_id, 
    n.event_dt, 
    n.sequence_number, 
    p.call_id as prior_call_id, 
    p.event_id as prior_event_id,
    p.event_dt as prior_event_dt,
    p.sequence_number as prior_sequence_number
FROM daily_events n
INNER JOIN daily_events p
    ON p.sequence_number = n.sequence_number - 1
WHERE n.event_id = '5047'
AND n.event_dt >= DATE( '01/06/2012' ) 
AND n.event_dt <= DATE( '01/07/2012' );

The query assumes that any event with a sequence number that differs by one is an appropriate match, and that the call_id doesn't also need to match. If that assumption is incorrect, then add AND n.call_id = p.call_id to the ON clause of the join.

Upvotes: 0

user359040
user359040

Reputation:

Assuming that the sequence number is sequential (ie. the next record always has a sequence number 1 greater than the current record), try:

select i.* 
from ivr_table i
where exists
(select 1
 from ivr_table ni
 where i.sequence + 1 = ni.sequence and ni.event_code = '5047')

EDIT: select null in subquery replaced with select 1

Upvotes: 0

Matt
Matt

Reputation: 4775

Assuming all you have is the particular event's "unique call id":

SELECT *
FROM tbl
WHERE sequence_number = (
    SELECT MAX(sequence_number)
    FROM tbl
    WHERE sequence_number = (
        SELECT sequence_number FROM tbl WHERE unique_id = PARTICULAR_EVENT_UNIQUE_ID
    )
);

If the sequence number of the particular event is known (instead or in addition to the unique call id), then the most inner select can be replaced in its entirety by that value.

Upvotes: 1

Related Questions