Finding the row before the negative value

Question

Post reply

Finding the row before the negative value

meryemkurs072

SSC Eights!

Points: 945

July 20, 2022 at 4:17 pm

#4066056

Hello

my table

CREATE TABLE #DETAIL
   (
    IDR   INT,
    ID    INT,
    AMOUNT FLOAT,
    BALANCE FLOAT
   )

INSERT INTO #DETAIL
VALUES (1,1,10,10),
       (2,1,10,20),
   (3,1,-5,15),
   (4,1,-10,5),
   (5,1,-10,-5),
   (6,1,10,5),
       (7,2,5,5),
   (8,2,5,10),
   (9,2,-5,5),
   (10,2,-10,-5),
   (11,2,-10,-15),
   (12,2,20,5)

I want to list the line before the negative line

The result I want

/*

IDR ID AMOUNT BALANCE

1 1 10 10

2 1 10 20

3 1 -5 15

4 1 -10 5

7 2 5 5

8 2 5 10

9 2 -5 5

*/

Mssql 2008

This topic was modified 2 years, 5 months ago by meryemkurs072.

Viewing 8 posts - 1 through 7 (of 7 total)

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

ScottPletcher SSC Guru Points: 101126 More actions · Answer 1

SELECT d1.*
FROM #DETAIL d1
WHERE d1.IDR < (SELECT MIN(d2.IDR) FROM #DETAIL d2 WHERE d2.ID = d1.ID AND d2.BALANCE < 0)

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

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 2

Hi

Thank you

Non-negative Id not coming?

What would be the reason

ıd = 3

If(OBJECT_ID('tempdb..#DETAIL') Is Not Null)
Begin
    Drop Table #DETAIL
End
 

CREATE TABLE #DETAIL
   (
    IDR   INT,
    ID    INT,
    AMOUNT FLOAT,
    BALANCE FLOAT
   )

INSERT INTO #DETAIL
VALUES 
      (1,1,10,10),
      (2,1,10,20),
      (3,1,-5,15),
      (4,1,-10,5),
      (5,1,-10,-5),
      (6,1,10,5),
      (7,2,5,5),
      (8,2,5,10),
      (9,2,-5,5),
      (10,2,-10,-5),
      (11,2,-10,-15),
      (12,2,20,5),
  (13,3,10,10),
  (14,3,20,30)


SELECT d1.*
FROM #DETAIL d1
WHERE d1.IDR < (SELECT MIN(d2.IDR) FROM #DETAIL d2 WHERE d2.ID = d1.ID AND d2.BALANCE < 0)

This reply was modified 2 years, 4 months ago by meryemkurs072.

Zidar SSC Enthusiast Points: 126 More actions · Answer 3

Can you clarify the question? What does he "line before negative line" means? Is it negative Amount or negative Balance? In the example, you are actually displaying all "positive" lines before the first "negative line". Then you show other negative lines.

Please clarify, in business terms not like "line before negative line", instead tell us like this "the first transaction when balance become negative, for each ID", or "the fist transaction with negative amount, and the preceding (1) line with positive balance/amount"

Zidar's Theorem: The best code is no code at all...

meryemkurs072 SSC Eights! Points: 945 More actions · Answer 4

Thank you.

Under normal conditions, when a row goes negative, the query lists the rows before that row.

However, if a movement has never been negative, it does not bring the relevant lines.

I want him to bring it.

Ken McKelvey SSCoach Points: 18861 More actions · Answer 5

WITH P2Ns
AS
(
    SELECT IDR, ID, AMOUNT, BALANCE
        ,CASE
            WHEN Balance < 0
                AND LAG(Balance) OVER (PARTITION BY ID ORDER BY IDR) >= 0
            THEN 1
            ELSE 0
        END AS P2N
    FROM #DETAIL
)
,Grps
AS
(
    SELECT IDR, ID, AMOUNT, BALANCE
        ,SUM(P2N) OVER (PARTITION BY ID ORDER BY IDR) AS Grp
    FROM P2Ns
)
SELECT IDR, ID, AMOUNT, BALANCE
FROM Grps
WHERE Grp = 0;

This reply was modified 2 years, 4 months ago by Ken McKelvey.

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

meryemkurs072 wrote:

Thank you.
Under normal conditions, when a row goes negative, the query lists the rows before that row.
However, if a movement has never been negative, it does not bring the relevant lines. I want him to bring it.

My problem with this whole thread is the title is "Finding the row before the negative value" (implying only ONE row) and then your example shows you listing all the positive rows before the negative ones except for row 6, which is positive. You also didn't stipulate which column had to be negative and left that to us to figure out. And although we could glean it from what you posted as an example, you didn't explicitly state that you didn't want any negative row even though the row before another negative is negative.

And what do you mean by "I want him to bring it." Who or what the heck is "him" and what precisely are you wanting to return from the data by row number?

--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)

Jeff Moden SSC Guru Points: 1004419 More actions · Answer 7

p.s. Since ID seems to be a sign of grouping, do you want this done by groups formed by ID or what? Is that why row 6 wasn't returned in your expected results?

If that's true, then just find the MAX IDR for each ID that has a negative balance and return all the rows for that ID that are less than that MAX IDR.

--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)