Finding Possible Duplicates

1

Hi,
I have a Invoice table with five columns - Invoice ID, Invoicenumber, Amount, date and Vendor. Invoice ID is PK.

I have to find the possible duplicates from this table.

Eg: 5 scenarios:
1.Same invoice number, Amount, date but vendor is slightly different
2.Same Amount, date, vendor but Invoice number is slightly different
3.Same Invoice number , date, vendor but Amount is slightly different
3.Same Invoice number , date, vendor but vendor is slightly different
5.All are same.

Can you please help me on how to implement these 5 scenarios and find the duplicates?

2

There are a couple of different techniques. I will give you the most easy to understand.

In scenario 1:

SELECT
Invoicenumber,
Amount,
Date,
COUNT(DISTINCT Vendor) AS NumberOfVendors
FROM
Invoices
GROUP BY
Invoicenumber,
Amount,
Date
HAVING COUNT(DISTINCT Vendor) > 1;

You can read more about this in :

Find Duplicate values in SQL (dataschool.com)

I would use the Row Number Approach, it's usefull in more situations but harder to understand.