Annie Jeba Annie Jeba - 1 month ago 6
SQL Question

Pick the missing records with latest dates in Oracle

I have 2 tables with only 3 common fields.
I wrote the below query to pick up the missing data with Latest date in table 1.

Employee Category Date_Field First_Name Last_Name Status Result
100 Type1 30/08/2010 A B Present Good
200 Type1 1/09/2010 C D Hello Alt
100 Type3 30/09/2010 A B
100 Type1 30/09/2012 A B


Employee Category Date_Field
100 Type1 03/11/2016
100 Type3 30/09/2010
100 Type4 11/10/2010
200 Type3 12/12/1989


My Query is

with x1 as (SELECT Employee,Category,Date_Field,First_Name,Last_Name,Status,Result,' ' as Somefield from table 1
where not exists

(select 1 from table2
where table1.employee=table2.employee and table1.category = table2.Category
and table1.Date_Field = table2.Date_Field)),

x2 as (select Employee,Category,Max(Date_Field) as DateField from x1 group by Employee)

select x1.Employee,x1.Category,x1.Date_Field,x1.First_Name,x1.Last_Name,x1.Status,x1.Result,x1.Somefield from x1,x2
where x1.Employee = x2.Employee and x1.Date_Field=x2.DateField and x1.Category=x2.Category
order by x1.Employee;


The Current Ouptput i get is

Employee Category Date_Field First_Name Last_Name Status Result
100 Type1 30/09/2012 A B
200 Type1 1/09/2010 C D Hello Alt


Can i get this query modified so that If there ia record for the same employee with latest date and category in table 2, my output should not have that record.

So the expected Output is

Employee Category Date_Field First_Name Last_Name Status Result
200 Type1 1/09/2010 C D Hello Alt


Your help is much appreciated

Answer

Hope you can ignore the datefield for the NOT EXISTS clause.

SELECT * FROM TABLE1 T1
WHERE NOT EXISTS
(
  SELECT 'x' FROM TABLE2 T2
  WHERE T1.EMPLOYEE = T2.EMPLOYEE
   AND T1.CATEGORY = T2.CATEGORY
   AND T2.DATEFIELD >= T1.DATEFIELD
)