How to compare complete sales orders against each other to look for differences?

Question

I'm after some advice after hitting a brick wall. I don't have specific errors or problems in my script - I'm just not sure how to approach the problem after many hours of researching and trial and error.

I've been asked to create a script that identifies whether a whole order is duplicated under another order number. It is classed as a duplicate if the customer reference, all items and their respective quantities are the same, but with a difference order number.

Based on the table below, the expected outcome is for both of these orders to show on the report because although the order numbers are different, the customer reference, items and quantities are identical.

Customer Reference	Order	Position	Item	Quantity
AAA123	001	1	Apples	12
AAA123	001	2	Oranges	3
AAA123	001	3	Pears	9
AAA123	001	4	Grapes	18
AAA123	002	1	Apples	12
AAA123	002	2	Oranges	3
AAA123	002	3	Pears	9
AAA123	002	4	Grapes	18

I have been successful in creating a report that checks for any item and quantity ordered under the same customer reference on a different order number, but this isn't helpful because we have lots of blanket orders, where a customer places one PO at the start of the year and uses it each time they place an order. This is why I've been asked to check whether the whole orders are identical - we don't ever see that.

Any advice you could give would be really appreciated. We use Oracle 11.2.0.3.

Thanks in advance.

Answer 1

If the ORDER_IDs is what you want (Based on the table below, the expected outcome is for both of these orders to show on the report), then you could just self join the table on all columns equal except ORDER_ID which should be not equal:

WITH
    tbl (CUSTOMER_REFERENCE, ORDER_ID, POSITION, ITEM, QUANTITY) AS
        (
            Select 'AAA123',    '001',  1,  'Apples',   12 From Dual Union All
            Select 'AAA123',    '001',  2,  'Oranges',   3 From Dual Union All
            Select 'AAA123',    '001',  3,  'Pears',     9 From Dual Union All
            Select 'AAA123',    '001',  4,  'Grapes',   18 From Dual Union All
            Select 'AAA123',    '002',  1,  'Apples',   12 From Dual Union All
            Select 'AAA123',    '002',  2,  'Oranges',   3 From Dual Union All
            Select 'AAA123',    '002',  3,  'Pears',     9 From Dual Union All
            Select 'AAA123',    '002',  4,  'Grapes',   18 From Dual
        )
Select      t.ORDER_ID
From        tbl t
Inner Join  tbl t1 ON(t1.CUSTOMER_REFERENCE = t.CUSTOMER_REFERENCE And t1.POSITION = t.POSITION And 
                      t1.ITEM = t.ITEM And t1.QUANTITY = t.QUANTITY And t1.ORDER_ID != t.ORDER_ID)
Group By    t.ORDER_ID
Order By    t.ORDER_ID

ORDER_ID
--------
001      
002  

... And if you want the complete data for both orders matching the condition then:

Select      t.*
From        tbl t
Inner Join  tbl t1 ON(t1.CUSTOMER_REFERENCE = t.CUSTOMER_REFERENCE And t1.POSITION = t.POSITION And 
                      t1.ITEM = t.ITEM And t1.QUANTITY = t.QUANTITY And t1.ORDER_ID != t.ORDER_ID)
Order By    t.ORDER_ID, t.POSITION

CUSTOMER_REFERENCE ORDER_ID   POSITION ITEM      QUANTITY
------------------ -------- ---------- ------- ----------
AAA123             001               1 Apples          12 
AAA123             001               2 Oranges          3 
AAA123             001               3 Pears            9 
AAA123             001               4 Grapes          18 
AAA123             002               1 Apples          12 
AAA123             002               2 Oranges          3 
AAA123             002               3 Pears            9 
AAA123             002               4 Grapes          18 

Answer 2

You can use MINUS to compare one order to another order to find the non-matching positions/item/quantity combinations and use NOT EXISTS to exclude those orders that do not match:

WITH orders (customer_reference, order_no, num_positions) AS (
  SELECT customer_reference,
         order_no,
         COUNT(position)
  FROM   table_name
  GROUP BY
         customer_reference,
         order_no
)
SELECT o1.customer_reference,
       o1.order_no,
       o2.order_no
FROM   orders o1
       INNER JOIN orders o2
       ON (   o1.customer_reference = o2.customer_reference
          AND o1.order_no           < o2.order_no
          AND o1.num_positions      = o2.num_positions)
WHERE NOT EXISTS (
  (
    SELECT position, item, quantity
    FROM   table_name t1
    WHERE  t1.customer_reference = o1.customer_reference
    AND    t1.order_no           = o1.order_no
  MINUS
    SELECT position, item, quantity
    FROM   table_name t2
    WHERE  t2.customer_reference = o2.customer_reference
    AND    t2.order_no           = o2.order_no
  )
  UNION ALL
  (
    SELECT position, item, quantity
    FROM   table_name t2
    WHERE  t2.customer_reference = o2.customer_reference
    AND    t2.order_no           = o2.order_no
  MINUS
    SELECT position, item, quantity
    FROM   table_name t1
    WHERE  t1.customer_reference = o1.customer_reference
    AND    t1.order_no           = o1.order_no
  )
)

Which, for the sample data:

CREATE TABLE table_name (Customer_Reference, Order_no, Position, Item, Quantity) AS
SELECT 'AAA123', '001', 1, 'Apples',  12 FROM DUAL UNION ALL
SELECT 'AAA123', '001', 2, 'Oranges',  3 FROM DUAL UNION ALL
SELECT 'AAA123', '001', 3, 'Pears',    9 FROM DUAL UNION ALL
SELECT 'AAA123', '001', 4, 'Grapes',  18 FROM DUAL UNION ALL
SELECT 'AAA123', '002', 1, 'Apples',  12 FROM DUAL UNION ALL
SELECT 'AAA123', '002', 2, 'Oranges',  3 FROM DUAL UNION ALL
SELECT 'AAA123', '002', 3, 'Pears',    9 FROM DUAL UNION ALL
SELECT 'AAA123', '002', 4, 'Grapes',  18 FROM DUAL UNION ALL
SELECT 'AAA123', '003', 1, 'Apples',  12 FROM DUAL UNION ALL
SELECT 'AAA123', '003', 2, 'Oranges',  3 FROM DUAL UNION ALL
SELECT 'AAA123', '003', 3, 'Pears',    9 FROM DUAL UNION ALL
SELECT 'AAA123', '003', 5, 'Grapes',  18 FROM DUAL UNION ALL
SELECT 'AAA123', '004', 1, 'Apples',  12 FROM DUAL UNION ALL
SELECT 'AAA123', '004', 2, 'Oranges',  3 FROM DUAL UNION ALL
SELECT 'AAA123', '004', 3, 'Pears',   10 FROM DUAL UNION ALL
SELECT 'AAA123', '004', 4, 'Grapes',  18 FROM DUAL;

Outputs:

CUSTOMER_REFERENCE	ORDER_NO	ORDER_NO
AAA123	001	002

fiddle

Answer 3

You can also use below solution to get the job done.

WITH TAB_WITH_NB_ORDER_ITEMS AS (
SELECT t.*, COUNT(*)OVER(PARTITION BY CUSTOMER_REFERENCE, ORDER_) nb_oi
FROM TEST_TAB_NAME t
)
SELECT T1.CUSTOMER_REFERENCE,
     T1.ORDER_             AS ORDER_1,
     T2.ORDER_             AS ORDER_2
FROM TAB_WITH_NB_ORDER_ITEMS T1
JOIN TAB_WITH_NB_ORDER_ITEMS T2
  ON (
     T1.CUSTOMER_REFERENCE = T2.CUSTOMER_REFERENCE 
     AND T1.ORDER_    < T2.ORDER_
/*Is the comparison on "position" column relevant here ??? */  
     AND t1.POSITION  = t2.POSITION
     AND T1.ITEM      = T2.ITEM 
     AND T1.QUANTITY  = T2.QUANTITY 
     AND T1.NB_OI     = T2.NB_OI
     )
GROUP BY T1.CUSTOMER_REFERENCE, T1.ORDER_, T2.ORDER_
HAVING COUNT(*) = MAX(T1.NB_OI) /* anyway t1.nb_oi = t2.nb_oi*/
;

demo on db<>fiddle