CODEWITHSUNDEEP
sql
Dorisacat
Dorisacat

Reputation: 139

SQL self-join compare current record with the record of the previous date

From the attached transaction table, the cardOldValue of each transaction should equal to the CardNewValue of the last transaction.

I want to identify the TransactionID and the CardNumber of some suspicious cards who's CardOldValue is greater than the CardNewValue of the previous transaction.

In the example, the transactionID '003' and cardNumber '1234' should be captured as it's greater than the previous transaction cardNewValue '50'.

The transaction table

Following is what I have so far, but it doesn't give me the data I am after. Apparently I am not quite sure how self-join work:

SELECT 
    f1.TransactionID, f1.cardNumber, f1.cardOldValue, 
    f2.cardNewValue, f1.transactionDate, f2.transactionDate
FROM 
    Transaction f1, Transaction F2
WHERE 
    f2.transactionDate = (SELECT MAX(transactionDate) 
                          FROM Transaction
                          WHERE transactionDate < f1.transactionDate
                            AND cardNumber = f2.cardNumber
                            AND f1.cardOldValue > f2.cardNewValue)

I tested the sub-query separately, it gives me the date that is just older than the current date. So could you please let me know how to self-join the table properly?

Upvotes: 4

Views: 7988

Answers (2)

wildplasser
wildplasser

Reputation: 44240

-- Finding previous record without a LAG() function:
SELECT
    this.TransactionID, this.cardNumber , this.transactionDate
        , this.cardOldValue, this.cardNewValue 
    , prev.TransactionID, prev.transactionDate
        , prev.cardNewValue
FROM Transaction this
JOIN Transaction prev
        ON this.cardNumber = prev.cardNumber            -- same card
        AND prev.transactionDate < this.transactionDate -- older date
        AND NOT EXISTS ( SELECT *                       -- no dates in between
                FROM Transaction mid
                WHERE mid.cardNumber = this.cardNumber
                AND mid.transactionDate < this.transactionDate
                AND mid.transactionDate > prev.transactionDate
        )
WHERE this.cardOldValue > prev.cardNewValue -- suspect
        ;

Upvotes: 8

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

As already suggested, LAG function can be used. The query should look like the following (not tested):

SELECT 
    f1.TransactionID, f1.cardNumber, f1.cardOldValue, f1.transactionDate,
    LAG(cardNewValue) OVER (PARTITION BY cardNumber ORDER BY transactionDate) AS cardNewValue,
    LAG(transactionDate) OVER (PARTITION BY cardNumber ORDER BY transactionDate) AS cardNewValue  
FROM Transaction f1

This greatly simplifies the query.

In MySQL these modern functions are not supported and have to be emulated.

Also, other SQL flavors support them: Oracle, PostgreSQL.

Upvotes: 2

Related Questions