SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Looking to match up balancing records


Looking to match up balancing records

Author
Message
rodrigueznestordavid
rodrigueznestordavid
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 5
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.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
SSC Eights!
SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)SSC Eights! (999 reputation)

Group: General Forum Members
Points: 999 Visits: 378
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.

Had read two times your post, Still not clarified.SmileSmile

Eirikur Eiriksson
Eirikur Eiriksson
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101101 Visits: 20937
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.

Please post the DDL (create table) scripts, sample data as an insert statement, what you have tried so far and the expected result set.
Cool

Sean Pearce
Sean Pearce
SSCrazy Eights
SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)SSCrazy Eights (9.9K reputation)

Group: General Forum Members
Points: 9897 Visits: 3511
CREATE TABLE #t
(RowNo INT IDENTITY(1, 1),
ID INT,
Debit DECIMAL(10, 2),
Credit DECIMAL(10, 2));

INSERT INTO
#t
VALUES
(13334357, 209.67, 0),
(13334357, 0, 209.67),
(13334357, 1000, 0),
(13334357, 0, 1000),
(64284964, 1500, 0),
(64284964, 0, 2700),
(64284964, 1200, 0);

WITH
CTE AS
(SELECT
*,
RunningTotal = SUM(Debit + (Credit * -1)) OVER (PARTITION BY ID ORDER BY ID ROWS UNBOUNDED PRECEDING)
FROM
#t),
CTE2 AS
(SELECT
*,
CASE
WHEN LAG(RunningTotal) OVER (PARTITION BY ID ORDER BY RowNo) IS NULL THEN 1
WHEN LAG(RunningTotal) OVER (PARTITION BY ID ORDER BY RowNo) = 0 THEN 1
ELSE 0
END AS Indicator
FROM
CTE)
SELECT
ID,
Debit,
Credit,
CHAR(SUM(Indicator) OVER (PARTITION BY ID ORDER BY RowNo) + 64) AS TypeCode
FROM
CTE2;

DROP TABLE #t;






The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search