CODEWITHSUNDEEP
sqlsql-servert-sql
Ramy
Ramy

Reputation: 33

Get most recent date when a value changed

I have two table of client account balance, the first one had the clients with debit account, the second had the details of account balance. Data will be like this:

TABLE A 

 id_account |  balance
 -----------|---------
    1       |  -50

TABLE B

id_account  |  data   | date
------------|---------|-----------
   1        |   -50   | 20/10/2018
   1        |   -30   | 09/10/2018
   1        |    30   | 01/10/2018
   1        |    20   | 25/09/2018
   1        |  -100   | 01/08/2018

i want to get the last date when he become debtor.

 id account |  balance | debit date
------------|----------|-----------
    1       |    -50   | 09/10/2018

Upvotes: 3

Views: 774

Answers (5)

Zohar Peled
Zohar Peled

Reputation: 82524

Update

Following Salman's comment and using the link to DBFiddle he published in his answer, I came up with a better solution:

WITH CTE1 AS
(
    SELECT account, Data, Date, 
           ISNULL(LAG(Data) OVER(PARTITION BY account ORDER BY Date), 1) As PrevData
    FROM TableB
), CTE2 AS
(
    SELECT account, Data, Date, PrevData,
           ROW_NUMBER() OVER(PARTITION BY account ORDER BY Date DESC) As rn
    FROM CTE1
    WHERE Data < 0 AND PrevData > 0
)

SELECT a.account, balance, b.Date
FROM tablea a
LEFT JOIN CTE2 b 
    ON a.account = b.account
    AND rn = 1

Note really simpler than Salman's answer, just another option that should give correct results. Note that if the account never had a negative sum in tableB, no date will be returned.

DB Fiddle link

First version

The simplest solution I found was selecting from TableA and joining a derived table based on TableB:

SELECT a.account, balance, b.Date
FROM TableA a
JOIN (
    SELECT account, Data, Date, 
           LAG(Data) OVER(PARTITION BY account ORDER BY Date) As PrevData
    FROM TableB
) b ON a.account = b.account
WHERE b.Data < 0 AND b.PrevData > 0

Upvotes: 1

lije
lije

Reputation: 420

Get the data for the dates he was a debtor and use Row_number() to list them in a descending order. Then joining the output with the other two tables and taking the 2 top will give the last date when he was a debtor. 

SELECT DISTINCT a.id_account, a.balance, c.date FROM  #tablea a join #tableb b
ON a.id_account=b.id_account 
JOIN (SELECT b.id_account, max(b.date) date, row_number() OVER (PARTITION BY id_account ORDER BY b.date DESC) rn FROM #tableb b GROUP BY b.id_account, b.data, b.date HAVING b.data <0) c 
ON c.id_account = a.id_account 
WHERE c.rn = 2

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272386

Apparently you can do this with NOT EXISTS:

SELECT a.*, debit_date
FROM @tablea AS a
OUTER APPLY (
    SELECT MIN(date) AS debit_date
    FROM @tableb AS b
    WHERE b.id_account = a.id_account
    AND   b.data < 0
    AND NOT EXISTS (
        SELECT 1
        FROM @tableb AS x
        WHERE x.id_account = b.id_account
        AND   x.date > b.date
        AND   x.data >= 0
    )
) AS OA

The OUTER APPLY part selects all rows where balance is negative (-50, -30, -100) then removes those for which a newer row exists with positive balance (for -100 the newer rows with positive balance are 20 and 30). After filtering you just need the minimum date.

DB Fiddle

Upvotes: 0

Sadeq Hatami
Sadeq Hatami

Reputation: 140

You can do it like this query

 Select 
    Id,
    balance,
    x.Date As debitDate
From TableA
Cross Apply
(
   Select top 1
         [Date]
   From TableB
   Where TableA.Id = TableB.Id
   Order by [Date] desc
) As x

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

This not exactly that you want, but as per sample data you can do :

select a.*, b.date as bebit_date
from tablea a outer apply
     ( select top (1) b.*
       from tableb b
       where b.id = a.id and b.data < a.balance
       order by b.date desc
     ) b;

If i go with your description them i would use row_number() :

select t.id, t.balance, t.date as debit_date
from (select a.*, b.*,
             row_number() over (partition by a.id order by b.date desc) as seq
      from tablea a inner join
           tableb b
           on b.id = a.id
     ) t
where seq = 2;

Upvotes: 0

Related Questions