Finding records that a negative number with a corresponding positive number

Question

Finding records that a negative number with a corresponding positive number

Briceston

SSCrazy

Points: 2899
More actions
September 6, 2019 at 9:00 pm

#3678096
Hello Gents,
I have a table that consists of a column that has both negative and positive transaction dollars. I need to identify all duplicate records, that have a negative dollar amount followed by a non-negative dollar amount. I need capture any negative then positive dollar amount regardless if the dollar amounts are the same or different.
Below are the sample records in the table.
```
AccountNumberTxnAmountTransactionDate
39593546-10836.686/15/2015
3959354611244.689/24/2015
00042495-836.68        4/13/2015
00042495756.01        4/16/2015
00041475-20936.784/13/2015
0004147510936.784/13/2015
00081479       -30436.784/26/2018
0008147930436.784/26/2018
```
Thanks in advance for your help

Viewing 11 posts - 1 through 10 (of 10 total)

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

Are you really still on SQL 2008? There is a simple way to do this in SQL 2012 and up, and SQL 2008 is no longer supported.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

ScottPletcher SSC Guru Points: 101097 More actions · Answer 2

a negative dollar amount followed by a non-negative dollar amount

Do you have something that can be sorted on to give the proper order of transactions? If so, it's easy to do. If not, it can't really be done, since SQL doesn't guarantee a certain row order.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

On SQL 2012 and later:

WITH CTE AS (
   SELECT AccountNumber, TxtAmount, PrevAmount = LAG(TxnAmount) OVER (PARTITION BY AccountNumber ORDER BY TransactionDate), TransactionDate
    FROM tbl
)
SELECT AccountNumber, txnAmount, Prevamount, TransactionDate
FROM   CTE
WHERE  sign(precAmount) = -1
  AND  Sign(prevAmount) = 1

If you are still on SQL 2008, it will be:

WITH CTE AS (
    SELECT AccountNumber, TxnAmount, TransactioNDate,
          rowno = row_number() OVER (PARTITION BY AccountNumber ORDER BY TransactionDate)
    FROM   tbl
)
SELECT a.AccountNumber, a.TxnAmount, b.TxnAmount, a.transactionDate, b.TransactionDate
FROM   CTE a
JOIN   CTE b ON A.AccountNumber = b.AccountNumber
            AND A.rowno = b.rowno - 1
WHERE  sign(a.TxnAmount) = -1
  AND  sign(b.TxnAmount) = 1

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

Erland Sommarskog wrote:

On SQL 2012 and later:

WITH CTE AS (
   SELECT AccountNumber, TxtAmount, PrevAmount = LAG(TxnAmount) OVER (PARTITION BY AccountNumber ORDER BY TransactionDate), TransactionDate
    FROM tbl
)
SELECT AccountNumber, txnAmount, Prevamount, TransactionDate
FROM   CTE
WHERE  sign(precAmount) = -1
  AND  Sign(prevAmount) = 1

If you are still on SQL 2008, it will be:

WITH CTE AS (
    SELECT AccountNumber, TxnAmount, TransactioNDate,
          rowno = row_number() OVER (PARTITION BY AccountNumber ORDER BY TransactionDate)
    FROM   tbl
)
SELECT a.AccountNumber, a.TxnAmount, b.TxnAmount, a.transactionDate, b.TransactionDate
FROM   CTE a
JOIN   CTE b ON A.AccountNumber = b.AccountNumber
            AND A.rowno = b.rowno - 1
WHERE  sign(a.TxnAmount) = -1
  AND  sign(b.TxnAmount) = 1

Consider that there are identical transaction dates in the given data, which means there's no guarantee that this will work correctly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

A correct observation Jeff, but since Briceton did not care to include anything else, I had to assume that the Transaction date is unique. If it is not, well, then Briceton will need to replace it or extended it so that he has something which is unqiue. If the ORDER BY clauses in the OVER clauses are not unique, the solutions I posted will not be deterministic.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 6

@ Briceston,

Considering the brief interlude between Erland and myself as well as Scott's previous early warning, do you have another column that could act as a temporal tie-breaker? For example (although not perfect), an IDENTITY column or (better yet) a TIME column? If not, this problem cannot be solved in a reliable manner... just like Scott said near the beginning of this thread.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Briceston SSCrazy Points: 2899 More actions · Answer 7

Gents,

First, thank you all for chiming in with the provided insight.

I have since upgraded to SQL Server 2017 for those who mentioned the usage of the older SQL Server 2008R2.

The 2008 version provided by Erland Sommarskog produces the intended output I'm looking for. However, when I used 2012 or later code in Sql server 2017, I received an error due to column 'precAmount'. Not really sure how this column is being derived.

Thanks,

Brice

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 8

Briceston wrote:

Gents,
First, thank you all for chiming in with the provided insight.
I have since upgraded to SQL Server 2017 for those who mentioned the usage of the older SQL Server 2008R2.
The 2008 version provided by Erland Sommarskog produces the intended output I'm looking for. However, when I used 2012 or later code in Sql server 2017, I received an error due to column 'precAmount'. Not really sure how this column is being derived.
Thanks,
Brice

None of that will matter if the TransactionDate and AccountNumber in your table do not form UNIQUE values throughout the table. 😉

I also believe that "precAmount" was a type-o that should have been "prevAmount".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 9

So here is a lesson for you: If you want to be spoonfed a working solution, you should include a CREATE TABLE script for your table and INSERT statements with sample data together with the expected results for the sample. This permits to copy and past into a query window to develop a tested solution. Without that you will get an untested solution, and you are expected to sort out typos and similar small errors on your own. And in either case, my hope is that you will actually look at the solution and try to understand it. If you had, you would easily have spotted the typo in the SQL 2012 solution - after all how far away from each other are the c and v keys on the keyboard?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

@ Briceston,

Please see the first link in my signature line below for why Erland is saying what most of us are thinking. We try to help a lot of people and would rather test our solutions against actual data. Posters can help themselves out an awful lot by posting "Readily Consumable" data. Erland did a great job posting without it and we just don't have the time to convert all manner of posts to "Readily Consumable" data. And, he's a little frustrated (as I would be) that you couldn't look at the code and figure out what the type-o was on your own.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)