I have a table like this:
| RecordID | Code | Amount |
| 1 | A | -500 |
| 2 | A | 500 |
| 3 | B | -500 |
| 4 | B | 300 |
| 5 | A | 500 |
The rule is that for a given Code if there are amounts that add up to 0, then I need to label the pairs for removal.
The tricky part is that there's no unique identifier and hence there's randomness. For example, since A on the first row is -500, A on the second row is 500, and A on the fifth row is also 500. Therefore, A on the first row adds either A on the second or the fifth row would result in 500. However, since there's only one -500, I would only need to remove one of the two 500s, not both. This means either row 2 or 5 -- both of which would be acceptable answers.
Please note that the answer I'm looking for should cover all scenarios, such as multiple -500 but one 500 and two -500 and two 500s (in which case all are marked for removal), etc. Only solutions that cover all possible scenarios would be marked accepted.
Outcome 1:
| RecordID | Code | Amount | Label |
| 1 | A | -500 | Remove |
| 2 | A | 500 | Remove |
| 3 | B | -500 | Keep |
| 4 | B | 300 | Keep |
| 5 | A | 500 | Keep |
Outcome 2:
| RecordID | Code | Amount | Label |
| 1 | A | -500 | Remove |
| 2 | A | 500 | Keep |
| 3 | B | -500 | Keep |
| 4 | B | 300 | Keep |
| 5 | A | 500 | Remove |
Any idea as to how I can achieve this?
