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
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.
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.
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)