Oracle SQL - duplicates within a date range
Posted on 2014-09-12
I'm thinking this is similar to finding duplicates, but slightly more involved.
I'm using Oracle 11g
I have three tables - Customer, Customer_Order & Order_Detail_History
The Customer Table is:
The Customer_order table is:
The Order_Detail_History is
Order_Detail_History contains millions of records.Customer_Order has hundreds and Customer has a hundred.
The Order_Detail_History table is filled upon finalization of an order.
The order details can be slightl different depending on who is inputting the information (long story).
I wish to QA the Order_Detail_History and make sure that there are no duplicate orders (different order_id, same detail)
I would like to do it based on start_date, end_date if possible.
I want to see the customer_name, the order_id, start & end dates of those orders in the Order_Detail_History.
Example - :
Select distinct a.order_id, c.Customer_name, b.date_start,b.date_end
from Order_Detail_History a
inner join Customer_Order b on a.order_id = b.order_id
inner join Customer c on c.customer_ID = b.Customer_ID
Order ID Customer_Name Start_Date End_Date
123 JOE 1/1/12 6/12/12 <----
213 JOE 1/1/12 7/15/12 <----
217 JOE 3/1/12 6/30/12 <----
333 JOE 2/14/13 2/25/13
145 SAM 1/1/14 3/14/14
These top three would have duplicates.
Is there a way to generate only these with a SQL query ?