Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

finding the min date of different dataset and pivoting

DevxMar 7 2020 — edited Mar 7 2020

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

Comments

Post Details

Added on Mar 7 2020
8 comments
175 views