I am trying to create a query that will return duplicate orders in my database. I consider an order to be potentially duplicate if it was made by the same customer, in the same month, the same products and the same quantity of products. For example, this is an extract from my orders table:
OrderID OrderDate ProductID ProductQuantity 1001 1/1/17 2001 4 1001 1/1/17 2002 5 1001 1/1/17 2003 6 1002 1/15/17 2001 4
I created a duplicate query that checks that OrderDate (Month(OrderDate)), the ProductID and ProductQuantity for duplicate entries. The problem I faced is that the query checks the duplicates in each row and the order is comprised of multiple rows. So if a customer purchased a number of products in a month and then made another order in the same month and one of the products has the same quantity as the previous order then the query will return a match. For example the query will consider order 1002 in the above table a duplicate to order to 1001 because ProductID 2001 has the same quantity. How can I make the query consider the whole order not only a single row of the order ?