CODEWITHSUNDEEP
sqloracle-databasenegative-number
Yte
Yte

Reputation: 21

How to find negative number in a column, which is followed by a positive number

I have a historical table XY with these contents:

 ID   Person_ID   Balance_on_account   ts
---- ----------- -------------------- ----------
 01   05          +10                  10.10.14
 02   05          -10                  20.10.14
 03   05          -50                  30.10.14
 04   05          +50                  30.10.14
 05   05          -10                  30.10.14
 06   06           11                  11.10.14
 07   06          -40                  15.10.14
 08   06           +5                  16.10.14
 09   06          -10                  30.10.14

and I need to create an SQL query which will give me those Person_ID's and timestamps where are

Like for Person_ID = 05 I would have the row with ID = 05, and for Person_ID = 06 the row with ID = 09.

Upvotes: 1

Views: 19554

Answers (2)

1010
1010

Reputation: 1848

I never used it, but you could try analytic LEAD function

SELECT * 
FROM (
    SELECT ID, Person_ID, Balance_on_account, ts
           LEAD (Balance_on_account, 1) 
           OVER (PARTITION BY Person_ID ORDER BY ID) next_balance
    FROM XY)
WHERE Balance_on_account < 0 and next_balance >= 0
ORDER BY ID

LEAD lets you access the following rows in a query without joining with itself. PARTITION BY groups rows by Person_ID so it doesn't mix different person's balances and ORDER BY defines the order within each group. The filtering cannot be done in the inner query because it'd filter out the rows with positive balance.

next_balance will be null for the last row.

source analytic functions and LEAD

Upvotes: 2

mhep
mhep

Reputation: 2139

The following query should give you the expected results provided the database platform you are using supports Common Table Expressions and Window Functions e.g. SQL Server 2008 and up.

SqlFiddle

WITH TsOrder AS
(
    SELECT
        Id
        , Person_Id
        , Balance_on_account
        , ts
        , ROW_NUMBER() OVER(PARTITION BY Person_Id
                            ORDER BY ts, Id) AS ts_Order

    FROM
        [TableName]
)

SELECT
    *

FROM
    TsOrder
    LEFT JOIN TsOrder AS NextTs
        ON  TsOrder.Person_id = NextTs.Person_Id
            AND TsOrder.ts_order = NextTs.ts_order - 1

WHERE
    TsOrder.Balance_on_account < 0
    AND NextTs.Balance_on_account > 0

Upvotes: 0

Related Questions