Is it possible to exclude rows from a select based on conditions of another row

Question

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)

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.

DECLARE @Sample TABLE
    (
    Ref VARCHAR(10) NOT NULL
    , Amount DECIMAL(10,2) NOT NULL
    , VoucherNo INT NOT NULL
    )

INSERT INTO @Sample
(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
FROM CTE_Voucher AS C
    INNER JOIN @Sample AS S ON S.VoucherNo = C.VoucherNo