Hi everyone,
i have a scenario where i want to find the min dates from multiple tables that contain different date field. let me explain further. please consider the following data
with summ as
(
SELECT 8521 AS cid, 'partial' as status, to_date('2/16/2015 8:54:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'partial' as status,to_date('2/15/2015 5:28:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 7364 AS cid, 'complete' as status ,to_date('2/16/2015 2:15:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'complete' as status ,to_date('2/15/2015 8:03:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 6361 AS cid, 'complete' as status ,to_date('2/15/2015 8:17:00 PM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'partial' as status , to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as ev_dt, 123 as v_id from dual
)
,orders as
(
SELECT 8521 AS cid, 'attending' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8521 AS cid, 'phone' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8521 AS cid, 'md' as data_val, to_date('2/16/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'attending' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'phone' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 1428 AS cid, 'md' as data_val, to_date('2/15/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'attending' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'phone' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 8965 AS cid, 'md' as data_val, to_date('2/15/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'attending' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'phone' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual union all
SELECT 6935 AS cid, 'md' as data_val, to_date('2/16/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as or_dt, 123 as v_id from dual
)
,detail as
(
SELECT 8521 AS cid, 'date_Read' as data_val, to_date('3/17/2015 8:17:00 AM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union all
SELECT 1428 AS cid, 'date_Read' as data_val, to_date('2/24/2015 4:59:00 PM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union all
SELECT 8965 AS cid, 'date_Read' as data_val, to_date('3/31/2015 8:01:00 PM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual union all
SELECT 6935 AS cid, 'date_Read' as data_val, to_date('2/25/2015 1:57:00 AM', 'mm/dd/yyyy hh:mi:ss am') as date_Read, 123 as v_id from dual
)
i want to join all 3 tables (summ, orders,detail) by cid, v_id and i want to find the following.
from summ table i want to find the row with min ev_dt where status is complete. from orders table i want to find the row with min or_dt
and from detail table i want to find out the row with min date_Read. my output should look like this
vi_id first_complete first_ordered first_read
123 2/15/2015 8:03:00 PM 2/15/2015 4:59:00 PM 2/24/2015 4:59:00 PM
my query looks like this but it is not completed
select * from (
SELECT row_number () over (partition by v_id , status order by ev_dt) as first_completed,
row_number () over (partition by v_id order by ord_dt) as first_ordered,
row_number () over (partition by v_id order by date_Read) as first_read
FROM summ a
JOIN orders b
on (a.v_id = b._vid)
JOIN detail c
on (a.v_id = c.v_id)
) where first_completed = 1 or (first_ordered = 1 and status='complete')
or first_read = 1
can someone help me modify my query so that i can get the output describe above? im using oracle 11g
thanks in advance