Looking to match up balancing records

• rodrigueznestordavid

  Grasshopper

  Points: 14

  More actions

  January 30, 2018 at 4:27 pm

  #343694

  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

  SSCarpal Tunnel

  Points: 4223

  More actions

  February 6, 2018 at 12:46 am

  #1978660

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

• Eirikur Eiriksson

  SSC Guru

  Points: 182911

  More actions

  February 6, 2018 at 2:15 am

  #1978665

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

• Sean Pearce

  SSCoach

  Points: 15999

  More actions

  February 6, 2018 at 2:49 am

  #1978669

  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[/url]

  @SeanPearceSQL

  About Me[/url]