Hello folks,
I currently have a working solution for this using PL/SQL but it would be nice to have this using SQL. Any help is appreciated and again, at your convenient time.
I am looking to pick up the latest date and the corresponding User who updated the record for a particular Student. There are two tables T1 and T2. The latest date may be the create_date or the modified_date of either T1 or T2.
Scripts for table creation and INSERT statements:
create table T1
( code varchar2(4),
create_date date,
create_userid varchar2(20),
modified_date date,
modify_userid varchar2(20));
create table T2
( code varchar2(4),
visit_id number,
visit_date date,
create_date date,
create_userid varchar2(20),
modified_date date,
modify_userid varchar2(20));
insert into T1 values ('1001',to_date('06-FEB-2013 09:12:12','DD-MON-YYYY HH24:Mi:SS'),'ROGER',to_date('12-APR-2013 13:01:12','DD-MON-YYYY HH24:Mi:SS'),'BRIAN');
insert into T2 values ('1001',1,to_date('10-JAN-2013','DD-MON-YYYY'), to_date('10-JAN-2013 14:12:12','DD-MON-YYYY HH24:Mi:SS'),'ROGER',to_date('12-MAR-2013 12:01:06','DD-MON-YYYY HH24:Mi:SS'),'AMY');
insert into T2 values ('1001',2,to_date('31-JAN-2013','DD-MON-YYYY'), to_date('12-MAY-2013 16:11:12','DD-MON-YYYY HH24:Mi:SS'),'GRACIE',null,null);
insert into T1 values ('1002',to_date('12-JAN-2013 11:12:13','DD-MON-YYYY HH24:Mi:SS'),'LYNNELLE',to_date('12-APR-2013 13:01:12','DD-MON-YYYY HH24:Mi:SS'),'BRIAN');
insert into T2 values ('1002',1,to_date('10-JAN-2012','DD-MON-YYYY'), to_date('10-JAN-2012 09:12:12','DD-MON-YYYY HH24:Mi:SS'),'ROGER',to_date('12-APR-2013 13:04:12','DD-MON-YYYY HH24:Mi:SS'),'AMY');
insert into T2 values ('1002',2,to_date('10-JAN-2013','DD-MON-YYYY'), to_date('12-JAN-2013 11:12:13','DD-MON-YYYY HH24:Mi:SS'),'JOHN',null,null);
insert into T1 values ('1003', to_date('04-FEB-2014 12:01:01', 'DD-MON-YYYY HH24:Mi:SS'), 'LYNNELLE', null, null);
What I would like to show for the three codes are the following records:
Code Table Date User ID
1001 T2 12-MAY-2013 16:11:12 GRACIE
1002 T2 12-APR-2013 13:04:12 AMY
1003 T1 04-FEB-2014 12:01:01 LYNNELLE
In the case, of Student 1001, the latest date is coming from the create_date of visit number = 2 while for Code 1002, the latest date is coming from modified_date for Visit 1 (Its 3 seconds later than the modified_date of T1). Finally, for Student 1003 (Who does not have any records in T2, the create_date is the only date and should be picked up.
Thanks in advance.