i have application keeps track when file being “attempted” move 1 server another, when has “succeeded” or “failed.” "attempt" should paired "success" or "failure." however, encountering orphaned attempts...meaning there have been attempts without success or failure reported. another question asked, able isolate e_ids in particular considered orphans. however, not want return "non-orphaned" transmissions in final output. consider following sample output:
in output above, example, e_id 000125 has completed transmission (attempt-->success) @ time 5/23/2016 11:37:09pm. not want see this. again, e_id 000672 has completed transmission(attempt-->failure) @ time 5/25/2016 1:28:36pm. not want see either. only want see orphaned transmissions, result set looking this:
it may worth noting completed transmissions occur within 1 second of each other (hence why in first sample output dates appear identical, when in reality differ milliseconds).
finally, query far follows
--this query shows data contact_id's known orphan select * ( select d.* ( select e_id, count(*) attempts e_table e_comment '%attempting%' , e_date >= '23-may-2016' , e_date <= '26-may-2016' group e_id ) full outer join ( select e_id, count(*) successes e_table e_comment '%successful%' , e_date >= '23-may-2016' , e_date <= '26-may-2016' group e_id ) s on s.e_id = a.e_id full outer join ( select e_id, count(*) failures e_table e_comment '%failed%' , e_date >= '23-may-2016' , e_date <= '26-may-2016' group e_id ) f on f.e_id = coalesce(a.e_id, s.e_id) full outer join ( select * e_table e_date >= '23-may-2016' , e_date <= '26-may-2016' ) d on a.e_id = d.e_id coalesce(attempts, 0) <> coalesce(successes, 0) + coalesce(failures, 0) ) minus ( --this i'm stuck. figure, minus, can remove --cases completed transmissions, showing orphans. ) any appreciated.
here solution not require joins; instead uses lead analytic function.
oracle setup:
create table e_table ( ce_id, e_id, e_comment, e_date ) select '472', '125', 'is attempting move', timestamp '2016-05-23 09:49:10' dual union select '678', '125', 'is attempting move', timestamp '2016-05-23 11:37:09' dual union select '724', '125', 'has moved', timestamp '2016-05-23 11:37:09' dual union select '983', '034', 'is attempting move', timestamp '2016-05-24 17:04:35' dual union select '643', '672', 'is attempting move', timestamp '2016-05-25 13:28:36' dual union select '026', '672', 'failed move', timestamp '2016-05-25 13:28:36' dual union select '087', '672', 'is attempting move', timestamp '2016-05-24 18:33:35' dual; query:
select ce_id, e_id, e_comment, e_date ( select e.*, lead( e_comment ) on ( partition e_id order e_date, decode( e_comment, 'is attempting move', 1, 2 ) ) next_comment e_table e ) ( next_comment null or next_comment = 'is attempting move' ) , e_comment = 'is attempting move'; output:
ce_id e_id e_comment e_date ----- ---- ---------------------- ----------------------------- 983 034 attempting move 2016-05-24 17:04:35.000000000 472 125 attempting move 2016-05-23 09:49:10.000000000 087 672 attempting move 2016-05-24 18:33:35.000000000 