remove unwanted rows

Question

Post reply

remove unwanted rows

GrassHopper

SSCarpal Tunnel

Points: 4473
More actions
October 10, 2022 at 12:07 am
Go to Answer
#4097253

I have a query that returns 9 rows of data. I want to remove the rows that cancel each other out by the amt column. See current results below and desired results. I am using RefDocNum and AcctDocNum1 as my key fields.
The LocDbtAmt is the column I want to use to remove rows that cancel each other out.
My desired rows to return would be :
AcctDocNum1
5110873185
5111183781
5111251198

DesNorton

SSC-Insane

Points: 24386

October 10, 2022 at 2:23 pm

Answer

#4097493

GrassHopper wrote:

I'm not looking to delete, just exclude. See my response to Des below. I made an adjustment to the data. I left out a column by mistake.

Simply include the extra field in the queries

WITH cteDbt AS (
  /* Uniquify Dr records */
  SELECT  dr.RefDocNum
        , dr.AcctDocNum1
        , dr.DocType1
        , dr.LocCrdtAmt
        , dr.LocDbtAmt
        , dr.LocDocAmt
        , drSeq = ROW_NUMBER() OVER (PARTITION BY dr.RefDocNum, dr.LocDbtAmt ORDER BY dr.AcctDocNum1 DESC)
  FROM #DocData AS dr
  WHERE dr.LocDbtAmt > 0
)
, cteCrt AS (
  /* Uniquify Cr records */
  SELECT  cr.RefDocNum
        , cr.AcctDocNum1
        , cr.DocType1
        , cr.LocCrdtAmt
        , cr.LocDbtAmt
        , cr.LocDocAmt
        , crSeq = ROW_NUMBER() OVER (PARTITION BY cr.RefDocNum, cr.LocCrdtAmt ORDER BY cr.AcctDocNum1 DESC)
  FROM #DocData AS cr
  WHERE cr.LocCrdtAmt > 0
)
/* Retrieve Dr records with no matching Cr record */
SELECT d.RefDocNum, d.AcctDocNum1, d.DocType1, d.LocCrdtAmt, d.LocDbtAmt, d.LocDocAmt
FROM cteDbt AS d
WHERE NOT EXISTS (SELECT 1
                 FROM cteCrt AS ccte
                 WHERE ccte.RefDocNum = d.RefDocNum
                   AND ccte.LocCrdtAmt = d.LocDbtAmt
                   AND ccte.crSeq = d.drSeq
                 )
UNION ALL
/* Retrieve Cr records with no matching Dr record */
SELECT c.RefDocNum, c.AcctDocNum1, c.DocType1, c.LocCrdtAmt, c.LocDbtAmt, c.LocDocAmt
FROM cteCrt AS c
WHERE NOT EXISTS (SELECT 1
                 FROM cteDbt AS dcte
                 WHERE dcte.RefDocNum = c.RefDocNum
                   AND dcte.LocDbtAmt = c.LocCrdtAmt
                   AND dcte.drSeq = c.crSeq
                 )
ORDER BY RefDocNum, AcctDocNum1;

How to post data/code on a forum to get the best help.

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 1

Hello,

would you delete the rows in the database, or in the result set?

And, please avoid to post photos, use code instead.

Kind regards,

Andreas

kaj SSCommitted Points: 1605 More actions · Answer 2

Perhaps something like this?

SELECT
    RefDocNum,
    MAX(AcctDocNum1) AS AcctDocNum1,
    DocType1,
    LocCrdtAmt,
    LocDbtAmt
FROM 
    dbo.yourTable
GROUP BY
    RefDocNum,
    DocType1,
    LocCrdtAmt,
    LocDbtAmt
HAVING
    COUNT(*) = 1

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

GrassHopper wrote:

I have a query that returns 9 rows of data. I want to remove the rows that cancel each other out by the amt column. See current results below and desired results. I am using RefDocNum and AcctDocNum1 as my key fields.
The LocDbtAmt is the column I want to use to remove rows that cancel each other out.
My desired rows to return would be :
AcctDocNum1
5110873185
5111183781
5111251198

You've been here long enough to know about providing sample data in consumable format.

Also, the word 'remove' suggests that you want to delete rows, is that correct? Or simply exclude them from your results?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DesNorton SSC-Insane Points: 24386 More actions · Answer 4

Setting Consumable data

IF OBJECT_ID(N'tempdb..#DocData', N'U') IS NOT NULL DROP TABLE #DocData;

CREATE TABLE #DocData (
    RefDocNum   char(10)      NOT NULL
  , AcctDocNum1 char(10)      NOT NULL
  , DocType1    char(2)       NOT NULL
  , LocCrdtAmt  decimal(18,2) NOT NULL
  , LocDbtAmt   decimal(18,2) NOT NULL
  , PRIMARY KEY (RefDocNum, AcctDocNum1)
);

INSERT INTO #DocData ( RefDocNum, AcctDocNum1, DocType1, LocCrdtAmt, LocDbtAmt )
VALUES ( '9118215185', '5110873185', 'K1', 0, 853.95 )
     , ( '9118215185', '5111183781', 'K2', 0, 5.19 )
     , ( '9118215185', '5111183782', 'K1', 0, 853.95 )
     , ( '9118215185', '5111189290', 'K1', 0, 853.95 )
     , ( '9118215185', '5111247172', 'R1', 853.95, 0 )
     , ( '9118215185', '5111247173', 'R1', 853.95, 0 )
     , ( '9118215185', '5111251198', 'R2', 6.37, 0 );

Querying the uncancelled data - A bit long winded, but seems to get the correct results

WITH cteDbt AS (
  /* Uniquify Dr records */  SELECT  dr.RefDocNum
        , dr.AcctDocNum1
        , dr.DocType1
        , dr.LocCrdtAmt
        , dr.LocDbtAmt
        , drSeq = ROW_NUMBER() OVER (PARTITION BY dr.RefDocNum, dr.LocDbtAmt ORDER BY dr.AcctDocNum1 DESC)
  FROM #DocData AS dr
  WHERE dr.LocDbtAmt > 0
)
, cteCrt AS (
  /* Uniquify Cr records */  SELECT  cr.RefDocNum
        , cr.AcctDocNum1
        , cr.DocType1
        , cr.LocCrdtAmt
        , cr.LocDbtAmt
        , crSeq = ROW_NUMBER() OVER (PARTITION BY cr.RefDocNum, cr.LocCrdtAmt ORDER BY cr.AcctDocNum1 DESC)
  FROM #DocData AS cr
  WHERE cr.LocCrdtAmt > 0
)
/* Retrieve Dr records with no matching Cr record */SELECT d.RefDocNum, d.AcctDocNum1, d.DocType1, d.LocCrdtAmt, d.LocDbtAmt
FROM cteDbt AS d
WHERE NOT EXISTS (SELECT 1
                 FROM cteCrt AS ccte
                 WHERE ccte.RefDocNum = d.RefDocNum
                   AND ccte.LocCrdtAmt = d.LocDbtAmt
                   AND ccte.crSeq = d.drSeq
                 )
UNION ALL
/* Retrieve Cr records with no matching Dr record */SELECT c.RefDocNum, c.AcctDocNum1, c.DocType1, c.LocCrdtAmt, c.LocDbtAmt
FROM cteCrt AS c
WHERE NOT EXISTS (SELECT 1
                 FROM cteDbt AS dcte
                 WHERE dcte.RefDocNum = c.RefDocNum
                   AND dcte.LocDbtAmt = c.LocCrdtAmt
                   AND dcte.drSeq = c.crSeq
                 )
ORDER BY RefDocNum, AcctDocNum1;

OUTPUT

RefDocNum   AcctDocNum1  DocType1  LocCrdtAmt  LocDbtAmt
----------  -----------  --------  ----------  ---------
9118215185  5110873185   K1        0.00        853.95
9118215185  5111183781   K2        0.00          5.19
9118215185  5111251198   R2        6.37          0.00

This reply was modified 2 years, 1 month ago by DesNorton.

How to post data/code on a forum to get the best help.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 5

Sorry for my lazyness in not providing the consumable data. I just realized I left out another column (LocDocAmt ) which should be the correct amt column. This changes the query a little and i'm trying to figure out the logic, can you help? And no, i'm not wanting to delete the rows. The desired outcome is the same rows I indicated before.

Consumable data:

IF OBJECT_ID(N'tempdb..#DocData', N'U') IS NOT NULL DROP TABLE #DocData;

CREATE TABLE #DocData (

RefDocNum char(10) NOT NULL

, AcctDocNum1 char(10) NOT NULL

, DocType1 char(2) NOT NULL

, LocCrdtAmt decimal(18,2) NOT NULL

, LocDbtAmt decimal(18,2) NOT NULL

, PRIMARY KEY (RefDocNum, AcctDocNum1)

);

INSERT INTO #DocData ( RefDocNum, AcctDocNum1, DocType1, LocCrdtAmt, LocDbtAmt, LocDocAmt )

VALUES ( '9118215185', '5110873185', 'K1', 0, 853.95, 853.95 )

, ( '9118215185', '5111183781', 'K2', 0, 5.19, 5.19 )

, ( '9118215185', '5111183782', 'K1', 0, 853.95, 853.95 )

, ( '9118215185', '5111189290', 'K1', 0, 853.95, 853.95 )

, ( '9118215185', '5111247172', 'R1', 853.95, 0, -853.95 )

, ( '9118215185', '5111247173', 'R1', 853.95, 0, -853.95 )

, ( '9118215185', '5111251198', 'R2', 6.37, 0, -6.37 );

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 6

I'm not looking to delete, just exclude. See my response to Des below. I made an adjustment to the data. I left out a column by mistake.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 7

DesNorton wrote:

GrassHopper wrote:

I'm not looking to delete, just exclude. See my response to Des below. I made an adjustment to the data. I left out a column by mistake.

Simply include the extra field in the queries

WITH cteDbt AS (
  /* Uniquify Dr records */
  SELECT  dr.RefDocNum
        , dr.AcctDocNum1
        , dr.DocType1
        , dr.LocCrdtAmt
        , dr.LocDbtAmt
        , dr.LocDocAmt
        , drSeq = ROW_NUMBER() OVER (PARTITION BY dr.RefDocNum, dr.LocDbtAmt ORDER BY dr.AcctDocNum1 DESC)
  FROM #DocData AS dr
  WHERE dr.LocDbtAmt > 0
)
, cteCrt AS (
  /* Uniquify Cr records */
  SELECT  cr.RefDocNum
        , cr.AcctDocNum1
        , cr.DocType1
        , cr.LocCrdtAmt
        , cr.LocDbtAmt
        , cr.LocDocAmt
        , crSeq = ROW_NUMBER() OVER (PARTITION BY cr.RefDocNum, cr.LocCrdtAmt ORDER BY cr.AcctDocNum1 DESC)
  FROM #DocData AS cr
  WHERE cr.LocCrdtAmt > 0
)
/* Retrieve Dr records with no matching Cr record */
SELECT d.RefDocNum, d.AcctDocNum1, d.DocType1, d.LocCrdtAmt, d.LocDbtAmt, d.LocDocAmt
FROM cteDbt AS d
WHERE NOT EXISTS (SELECT 1
                 FROM cteCrt AS ccte
                 WHERE ccte.RefDocNum = d.RefDocNum
                   AND ccte.LocCrdtAmt = d.LocDbtAmt
                   AND ccte.crSeq = d.drSeq
                 )
UNION ALL
/* Retrieve Cr records with no matching Dr record */
SELECT c.RefDocNum, c.AcctDocNum1, c.DocType1, c.LocCrdtAmt, c.LocDbtAmt, c.LocDocAmt
FROM cteCrt AS c
WHERE NOT EXISTS (SELECT 1
                 FROM cteDbt AS dcte
                 WHERE dcte.RefDocNum = c.RefDocNum
                   AND dcte.LocDbtAmt = c.LocCrdtAmt
                   AND dcte.drSeq = c.crSeq
                 )
ORDER BY RefDocNum, AcctDocNum1;

zero rows were returned. Didn't work for me. I even made a change in the where clause because I have negative amounts :

Where dr.LocDocAmt <> 0

DesNorton SSC-Insane Points: 24386 More actions · Answer 8

My code works with the data that you provided. I cant test against the unknown

How to post data/code on a forum to get the best help.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 9

Try using the LocDocAmt only...that's the only column that matters. Also, LocDocAmt has negative amounts. The other amount columns will throw off the results in different examples.

This reply was modified 2 years, 1 month ago by GrassHopper.

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 10

GrassHopper wrote:

Try using the LocDocAmt only...that's the only column that matters. Also, LocDocAmt has negative amounts. The other amount columns will throw off the results in different examples.

Actually, I think I just saw my mistake...Let me try again.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

GrassHopper wrote:

Try using the LocDocAmt only...that's the only column that matters. Also, LocDocAmt has negative amounts. The other amount columns will throw off the results in different examples.

How about you trying to provide representative data instead?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

GrassHopper SSCarpal Tunnel Points: 4473 More actions · Answer 12

GrassHopper

SSCarpal Tunnel

Points: 4473

October 10, 2022 at 4:36 pm

#4097567

Worked! Thank you Des.

MarkP Old Hand Points: 305 More actions · Answer 13

Is there a business rule for which of the three values are cancelled out by the two 853.95 entries?

Just curious because the solution from Des may not be bulletproof without that extra check.