Finding the row before the negative value

  • 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 6 months, 2 weeks ago by  meryemkurs072.
  • 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) A socialist is someone who give you the shirt off *someone else's* back.

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

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

  • 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 5 months, 4 weeks ago by  Ken McKelvey.
  • 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)

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

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

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