Home Forums SQL Server 2014 Development - SQL Server 2014 Looking to match up balancing records RE: Looking to match up balancing records
February 6, 2018 at 12:46 am
rodrigueznestordavid - Tuesday, January 30, 2018 4:27 PMI have a view that is similar to the following one but with hundreds of recordsI 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/Cto 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_CODEbut 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.
Had read two times your post, Still not clarified.🙂🙂