My data set is quite simple;

Ref          Value      Voucher No
------------ ---------- ----------
M019922      -154.80    100081968
M019922A     154.80     100081968
M019922B     -154.80    100081991

These represent three lines in a sales ledger. The ref without a suffix is the original transaction. The ref with the A suffix is a reversal of the original transaction (it doesn't matter why, just that it was reversed). The ref with suffix B is the new, correct transaction.

So I have three scenarios to cover, and for simplicity I would like to use just one query, but if that's not possible so be it

Scenario 1

There is only a single transaction with no suffix. This means it's just an invoice we have processed, so I want to return the first row.

Scenario 2

There are two rows, one with no suffix and one with an A suffix. In this case I am not interested in either row, as we have just reversed the transaction.

Scenario 3

As above, I have three rows. In this case, I am only interested in the row with the B suffix.

There must be something I can do using the Voucher No, excluding the duplicates, bit I can't see what the answer is.

I don't think this is possible, but I am happy to be proved wrong.

Before you ask, I have interrogated the rest of the database to see if I can find a flag that tells me a transaction has been reversed out. This is a very old system......(I have the original invoice from 1984)


1 Answer 1


Given your requirements, this should work for you, I think. If you have another table with just the vouchers in it, that could help it along, as well as an index on the VoucherNo column.

    , Amount DECIMAL(10,2) NOT NULL
    , VoucherNo INT NOT NULL

(Ref, Amount, VoucherNo)
VALUES ('M019922', -154.80, 100081968)
    , ('M019922A', 154.80, 100081968)
    , ('M019922B', -154.80, 100081991)

;WITH CTE_Voucher AS
    SELECT S.VoucherNo
        , SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) AS TotalAmount
    FROM @Sample AS S
    GROUP BY S.VoucherNo
    HAVING SUM(COALESCE(S.Amount, CONVERT(DECIMAL(10,2), 0.0))) <> CONVERT(DECIMAL(10,2), 0.0)
SELECT C.VoucherNo
    , S.Ref
    , S.Amount
    INNER JOIN @Sample AS S ON S.VoucherNo = C.VoucherNo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.