rodrigueznestordavid - Tuesday, January 30, 2018 4:27 PM

I have a view that is similar to the following one but with hundreds of records

I need to find a way to connect records that balance each other out so I can pass over the same Type Code to all of them.
So the second record balances out the first one (credits = debits) so I would want to give the second record the Type Code A.
The third record is then balanced out by the fourth so I would want to give the fourth record type code B.
The 5th record combined with the 7th one balance out the 6th one so I would want to give the 5th and 7th records type code A.

In some cases it is a 1:1 relationship so I initially thought of doing something like this:
FROM Original_Table OG
LEFT JOIN (SELECT ID, VALUE, DEBIT_CREDIT_CD, TYPE_CODE FROM Original_Table) Q
ON Q.ID = OG.ID
AND Q.VALUE = OG.VALUE
AND Q.D/C <> OG.D/C

to then have a condition such as this:
  CASE
   WHEN OG.TYPE_CODE IS NULL THEN Q.TYPE_CODE
   ELSE OG.TYPE_CODE
   END AS TYPE_CODE

but this doesn't work when the relationship between credits and debits is 1-to-many.
If anyone has any ideas I could really use them. Been working on this for about a week and due to my lack of experience with SQL I haven't found a way to do it.