Start with a reliable invoice table

Your matching process needs stable invoice-level fields. A useful starting structure might include:

• InvoiceID
• SupplierID
• InvoiceNumber
• InvoiceDate
• InvoiceAmount
• PurchaseOrderNumber
• PaymentReference
• CreatedDateTime
• PaymentStatus

CREATE TABLE dbo.SupplierInvoice
(
    InvoiceID            bigint          NOT NULL PRIMARY KEY,
    SupplierID           int             NOT NULL,
    InvoiceNumber        nvarchar(100)   NULL,
    InvoiceDate          date            NOT NULL,
    InvoiceAmount        decimal(18,2)   NOT NULL,
    PurchaseOrderNumber  nvarchar(100)   NULL,
    PaymentReference     nvarchar(100)   NULL,
    CreatedDateTime      datetime2       NOT NULL,
    PaymentStatus        nvarchar(30)    NULL
);

Normalise invoice references before matching

Differences such as spaces, dashes and character case should not automatically prevent two otherwise similar invoice references from being compared.

SELECT
    InvoiceID,
    SupplierID,
    InvoiceNumber,

    UPPER(
        REPLACE(
            REPLACE(
                REPLACE(
                    LTRIM(RTRIM(InvoiceNumber)),
                    ' ',
                    ''
                ),
                '-',
                ''
            ),
            '/',
            ''
        )
    ) AS NormalisedInvoiceNumber,

    InvoiceDate,
    InvoiceAmount,
    PurchaseOrderNumber

FROM dbo.SupplierInvoice;

Examples:

INV-10482   → INV10482
INV 10482   → INV10482
inv/10482   → INV10482

Find the strongest duplicate candidates

Start with pairs that match on supplier, amount and normalised invoice number.

WITH InvoiceBase AS
(
    SELECT
        InvoiceID,
        SupplierID,
        InvoiceDate,
        InvoiceAmount,
        PurchaseOrderNumber,

        UPPER(
            REPLACE(
                REPLACE(
                    REPLACE(
                        LTRIM(RTRIM(InvoiceNumber)),
                        ' ',
                        ''
                    ),
                    '-',
                    ''
                ),
                '/',
                ''
            )
        ) AS NormalisedInvoiceNumber

    FROM dbo.SupplierInvoice
)

SELECT
    A.InvoiceID AS InvoiceID_A,
    B.InvoiceID AS InvoiceID_B,
    A.SupplierID,
    A.NormalisedInvoiceNumber,
    A.InvoiceAmount,
    A.InvoiceDate AS InvoiceDate_A,
    B.InvoiceDate AS InvoiceDate_B

FROM InvoiceBase A

INNER JOIN InvoiceBase B
    ON  A.SupplierID = B.SupplierID
    AND A.NormalisedInvoiceNumber = B.NormalisedInvoiceNumber
    AND A.InvoiceAmount = B.InvoiceAmount
    AND A.InvoiceID < B.InvoiceID;

The final condition is important:

A.InvoiceID < B.InvoiceID

It prevents the same pair appearing twice as A/B and B/A, and prevents an invoice matching itself.

Extend the search to near duplicates

Exact matching will miss cases where the same invoice has been entered with a slightly different reference or date.

Create a broader candidate set using conditions such as:

• Same SupplierID
• Same InvoiceAmount
• Invoice dates within a defined window
• Same purchase order
• Same or similar normalised invoice reference

SELECT
    A.InvoiceID AS InvoiceID_A,
    B.InvoiceID AS InvoiceID_B,

    A.SupplierID,
    A.InvoiceAmount,

    A.InvoiceDate AS InvoiceDate_A,
    B.InvoiceDate AS InvoiceDate_B,

    ABS(
        DATEDIFF(
            day,
            A.InvoiceDate,
            B.InvoiceDate
        )
    ) AS DateDifferenceDays

FROM dbo.SupplierInvoice A

INNER JOIN dbo.SupplierInvoice B
    ON  A.SupplierID = B.SupplierID
    AND A.InvoiceAmount = B.InvoiceAmount
    AND A.InvoiceID < B.InvoiceID

WHERE
    ABS(
        DATEDIFF(
            day,
            A.InvoiceDate,
            B.InvoiceDate
        )
    ) <= 14;

Score the evidence instead of using one rule

A weighted score makes it easier to distinguish a weak coincidence from a very strong duplicate candidate.

An illustrative weighting model could be:

• Same supplier: 20 points
• Same amount: 25 points
• Same normalised invoice number: 35 points
• Invoice dates within seven days: 10 points
• Same purchase order: 10 points

SELECT
    A.InvoiceID AS InvoiceID_A,
    B.InvoiceID AS InvoiceID_B,

    (
        CASE
            WHEN A.SupplierID = B.SupplierID
            THEN 20
            ELSE 0
        END
        +
        CASE
            WHEN A.InvoiceAmount = B.InvoiceAmount
            THEN 25
            ELSE 0
        END
        +
        CASE
            WHEN A.NormalisedInvoiceNumber =
                 B.NormalisedInvoiceNumber
            THEN 35
            ELSE 0
        END
        +
        CASE
            WHEN ABS(
                DATEDIFF(
                    day,
                    A.InvoiceDate,
                    B.InvoiceDate
                )
            ) <= 7
            THEN 10
            ELSE 0
        END
        +
        CASE
            WHEN A.PurchaseOrderNumber =
                 B.PurchaseOrderNumber
            THEN 10
            ELSE 0
        END
    ) AS RiskScore

FROM InvoiceBase A

INNER JOIN InvoiceBase B
    ON  A.SupplierID = B.SupplierID
    AND A.InvoiceID < B.InvoiceID;

You could then translate the score into bands:

CASE

    WHEN RiskScore >= 80
        THEN 'High'

    WHEN RiskScore >= 55
        THEN 'Medium'

    ELSE 'Low'

END AS RiskBand

Persist a finance review queue

Do not force analysts to rerun a query and remember what they reviewed yesterday.

Create a controlled exception table containing:

• CandidatePairID
• InvoiceID_A
• InvoiceID_B
• RiskScore
• RiskBand
• DetectedDateTime
• ReviewStatus
• ReviewedBy
• ReviewedDateTime
• ReviewOutcome
• ReviewComment

CREATE TABLE dbo.InvoiceRiskQueue
(
    CandidatePairID     bigint IDENTITY PRIMARY KEY,

    InvoiceID_A         bigint NOT NULL,
    InvoiceID_B         bigint NOT NULL,

    RiskScore           int NOT NULL,
    RiskBand            nvarchar(20) NOT NULL,

    DetectedDateTime    datetime2 NOT NULL,

    ReviewStatus        nvarchar(30) NOT NULL,
    ReviewedBy          nvarchar(200) NULL,
    ReviewedDateTime    datetime2 NULL,

    ReviewOutcome       nvarchar(50) NULL,
    ReviewComment       nvarchar(1000) NULL
);

Put the control under Power BI oversight

Once the review queue exists, Power BI can tell management whether the control is operating effectively.

Useful measures include:

Flagged Invoice Pairs =
COUNTROWS(
    'Invoice Risk Queue'
)

High Risk Pairs =
CALCULATE(
    [Flagged Invoice Pairs],
    'Invoice Risk Queue'[RiskBand] = "High"
)

Open Reviews =
CALCULATE(
    [Flagged Invoice Pairs],
    'Invoice Risk Queue'[ReviewStatus] = "Open"
)

Confirmed Duplicate Pairs =
CALCULATE(
    [Flagged Invoice Pairs],
    'Invoice Risk Queue'[ReviewOutcome]
        = "Confirmed Duplicate"
)

You can then analyse risk by supplier, reviewer, month, business entity, category or payment status.