sql - How can I find the next related (but slightly different) transaction in an Oracle table, possibly using LEAD? -

i have transaction table large number of events number of event types. i'm looking analysis on 2 related event types: transit-send , transit-receive. sample of table looks this:

 itemid | eventtype | transactiondate --------|-----------|-----------------  11111  | send      | 2013-07-02  22222  | receive   | 2013-07-02  33333  | receive   | 2013-07-03  22222  | send      | 2013-07-03  11111  | receive   | 2013-07-04  55555  | send      | 2013-07-05  22222  | receive   | 2013-07-06  44444  | send      | 2013-07-07  22222  | send      | 2013-07-07  44444  | receive   | 2013-07-08  55555  | receive   | 2013-07-09  22222  | receive   | 2013-07-10  33333  | send      | 2013-07-11   

what need find each pairing of send-receives, receive first following send: 11111 sent out on 7/2 , received on 7/4. 22222 sent on 7/3 , received on 7/6. however, 22222 received on 7/2 , 7/10.

i tried using join quick results:

select a.itemid, a.eventtype, a.transactiondate, b.eventtype, b.transactiondate, b.transactiondate - a.transactiondate "days" transactions a, transactions b a.itemid = b.itemid , a.eventtype = 'send' , b.eventtype = 'receive' , a.transactiondate < b.transactiondate 

i knew wouldn't me actual results wanted, quick , dirty approximation. issue return multiple rows same send every receive comes after (note duplication of first 22222 send transaction):

 a.itemid | a.eventtype | a.transactiondate | b.eventtype | b.transactiondate | days ----------|-------------|-------------------|-------------|-------------------|------  11111    | send        | 2013-07-02        | receive     | 2013-07-04        | 2  22222    | send        | 2013-07-03        | receive     | 2013-07-06        | 3  22222    | send        | 2013-07-03        | receive     | 2013-07-10        | 7  22222    | send        | 2013-07-07        | receive     | 2013-07-10        | 3  44444    | send        | 2013-07-07        | receive     | 2013-07-08        | 1  55555    | send        | 2013-07-05        | receive     | 2013-07-09        | 4 

google suggests might use lead analytic function. seems promising, given i'm not familiar it, i'm not sure how (or if) can make fit model linking lines aren't directly paired (find next transaction 11111, regardless of type). i'm hung trying limit first transaction sends, , second receives. how can make work?

perhaps help:

select t.*,        lead(eventtype) on (partition itemid order transactiondate) nexteventtype,        lead(transactiondate) on (partition itemid order transactiondate) nexteventtype, transactions t 

you can calculate days, need use subquery:

select t.*,        (case when nexteventtype = 'receive' nexteventdate - eventdate end) days (select t.*,              lead(eventtype) on (partition itemid order transactiondate) nexteventtype,              lead(transactiondate) on (partition itemid order transactiondate) nexteventdate       transactions t      ) t t.eventtype = 'send' 

there 2 challenges. 1 happens if there 2 sends in row same item, no receive. detect situation , assign receive second send.

the other timing. if have send , receive event on same date, there no enough information know goes first. pose problem. data not have such examples. solution include time stamp, if situation occur.