Oracle: associate a date with the earliest in a list without reuse
with t1(ENC_ID, CONTACT, EVENT_TIME) as( select 13991333, 1, '08/13/2012 22:00:36' from dual union all select 13991333, 1, '08/13/2012 22:25:21' from dual union all select 13991333, 1, '08/13/2012 22:26:14' from dual union all select 13991333, 1, '08/13/2012 22:36:42' from dual union all select 13991333, 2, '08/13/2012 22:29:40' from dual),t2(ENC_ID,CONTACT,EVENT_TIME) as( select 13991333, 1, '08/13/2012 22:25:40' from dual union all select 13991333, 1, '08/13/2012 22:29:31' from dual union all select 13991333, 1, '08/13/2012 22:30:47' from dual union all select 13991333, 1, '08/13/2012 22:31:35' from dual union all select 13991333, 1, '08/13/2012 22:31:49' from dual union all select 13991333, 2, '08/13/2012 22:31:14' from dual)select enc_id , contact , req , min(res) resp from (select q.enc_id , q.contact , q.req , case when resp > req then nth_value(q.resp, q.gid) over(partition by q.enc_id, q.contact, gid order by q.req, q.gid) end res from (select t1.enc_id , t1.contact , t1.event_time req , dense_rank() over (partition by t1.enc_id, t1.contact order by t1.event_time) gid , t2.event_time resp from t1 left outer join t2 on (t1.enc_id = t2.enc_id and t1.contact = t2.contact) order by 1,2,3,5 ) q )group by enc_id , contact , reqorder by enc_id , contact , reqResult:
ENC_ID CONTACT REQ RESP---------- ---------- ------------------- ------------------- 13991333 1 08/13/2012 22:00:36 08/13/2012 22:25:40 13991333 1 08/13/2012 22:25:21 08/13/2012 22:29:31 13991333 1 08/13/2012 22:26:14 08/13/2012 22:30:47 13991333 1 08/13/2012 22:36:42 13991333 2 08/13/2012 22:29:40 08/13/2012 22:31:14What we are doing here is indexing groups of requests using dense_rank analytical function:
select t1.enc_id , t1.contact , to_date(t1.event_time, 'mm/dd/yyyy hh24:mi:ss') req , dense_rank() over (partition by t1.enc_id, t1.contact order by to_date(t1.event_time, 'mm/dd/yyyy hh24:mi:ss')) gid , to_date(t2.event_time, 'mm/dd/yyyy hh24:mi:ss') resp From t1 left outer join t2 on (t1.enc_id = t2.enc_id and t1.contact = t2.contact) order by 1,2,3,5Result:
ENC_ID CONTACT REQ GID RESP---------- ---------- ------------------- ---------- ------------------- 13991333 1 08/13/2012 22:00:36 1 08/13/2012 22:25:40 13991333 1 08/13/2012 22:00:36 1 08/13/2012 22:29:31 13991333 1 08/13/2012 22:00:36 1 08/13/2012 22:30:47 13991333 1 08/13/2012 22:00:36 1 08/13/2012 22:31:35 13991333 1 08/13/2012 22:00:36 1 08/13/2012 22:31:49 13991333 1 08/13/2012 22:25:21 2 08/13/2012 22:25:40 13991333 1 08/13/2012 22:25:21 2 08/13/2012 22:29:31 13991333 1 08/13/2012 22:25:21 2 08/13/2012 22:30:47 13991333 1 08/13/2012 22:25:21 2 08/13/2012 22:31:35 13991333 1 08/13/2012 22:25:21 2 08/13/2012 22:31:49 13991333 1 08/13/2012 22:26:14 3 08/13/2012 22:25:40 13991333 1 08/13/2012 22:26:14 3 08/13/2012 22:29:31 13991333 1 08/13/2012 22:26:14 3 08/13/2012 22:30:47 13991333 1 08/13/2012 22:26:14 3 08/13/2012 22:31:35 13991333 1 08/13/2012 22:26:14 3 08/13/2012 22:31:49 13991333 1 08/13/2012 22:36:42 4 08/13/2012 22:25:40 13991333 1 08/13/2012 22:36:42 4 08/13/2012 22:29:31 13991333 1 08/13/2012 22:36:42 4 08/13/2012 22:30:47 13991333 1 08/13/2012 22:36:42 4 08/13/2012 22:31:35 13991333 1 08/13/2012 22:36:42 4 08/13/2012 22:31:49 13991333 2 08/13/2012 22:29:40 1 08/13/2012 22:31:14And then picking nth value of response according to the index of group of requests. So, for the first group of requests it will be the first value of the response time in the group, for the second group of requests it will be the second value of the response time in the group and so forth.
Update (mimic behavior of nth_value analytical function for 10g)
SQL> with t1(ENC_ID, CONTACT, EVENT_TIME) as( 2 select 13991333, 1, '08/13/2012 22:00:36' from dual union all 3 select 13991333, 1, '08/13/2012 22:25:21' from dual union all 4 select 13991333, 1, '08/13/2012 22:26:14' from dual union all 5 select 13991333, 1, '08/13/2012 22:36:42' from dual union all 6 select 13991333, 2, '08/13/2012 22:29:40' from dual 7 ), 8 t2(ENC_ID,CONTACT,EVENT_TIME) as( 9 select 13991333, 1, '08/13/2012 22:25:40' from dual union all 10 select 13991333, 1, '08/13/2012 22:29:31' from dual union all 11 select 13991333, 1, '08/13/2012 22:30:47' from dual union all 12 select 13991333, 1, '08/13/2012 22:31:35' from dual union all 13 select 13991333, 1, '08/13/2012 22:31:49' from dual union all 14 select 13991333, 2, '08/13/2012 22:31:14' from dual 15 ), 16 t3 as 17 ( 18 select z.* 19 , row_number() over (partition by enc_id, contact, req order by req) rn 20 from ( 21 select t1.enc_id 22 , t1.contact 23 , t1.event_time req 24 , dense_rank() over (partition by t1.enc_id, t1.contact order by t1.event_time) gid 25 , t2.event_time resp 26 from t1 27 left outer join t2 28 on (t1.enc_id = t2.enc_id and 29 t1.contact = t2.contact) 30 order by 1,2,3,5 31 ) z 32 ) 33 select enc_id , contact, req, min(resp) resp 34 from ( 35 select q.enc_id 36 , q.contact 37 , q.req 38 , case 39 when min(case rn when gid then resp end) over(partition by enc_id, contact, req order by req) > req 40 then min(case rn when gid then resp end) over(partition by enc_id, contact, req order by req) 41 end resp 42 from t3 q 43 ) 44 group by enc_id 45 , contact 46 , req 47 order by enc_id 48 , contact 49 , req 50 ;Result: ENC_ID CONTACT REQ RESP---------- ---------- ------------------- ------------------- 13991333 1 08/13/2012 22:00:36 08/13/2012 22:25:40 13991333 1 08/13/2012 22:25:21 08/13/2012 22:29:31 13991333 1 08/13/2012 22:26:14 08/13/2012 22:30:47 13991333 1 08/13/2012 22:36:42 13991333 2 08/13/2012 22:29:40 08/13/2012 22:31:14
You are very close... try this:
SELECT RQ.ENC_ID, RQ.CONTACT, RQ.EVENT_TIME REQ_EVENT_TIME, min(RS.EVENT_TIME) RES_EVENT_TIMEFROM RQLEFT OUTER JOIN RS ON RQ.ENC_ID=RS.ENC_ID AND RQ.CONTACT=RS.CONTACT AND RS.EVENT_TIME>RQ.EVENT_TIMEGROUP BY RQ.ENC_ID, RQ.CONTACT, RQ.EVENT_TIMEORDER BY 1,2,3,4