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

Question

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

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?

[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, May 21 '16 at 09:49
If you have a DBMS that supports [modern SQL](http://modern-sql.com/slides) you can use the `lag()` function — , May 21 '16 at 09:53

score 8 · Accepted Answer · answered May 21 '16 at 11:35

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

This is exactly what I am looking for. Thanks mate. – Dorisacat May 21 '16 at 14:00 — Dorisacat, May 21 '16 at 14:00

score 2 · Answer 2 · edited May 23 '17 at 11:59

2

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.

edited May 23 '17 at 11:59

Community

1
1

answered May 21 '16 at 10:05

Alexei - check Codidact

22,016
16
145
164

1

`lag()` is nothing specific to T-SQL. It's part of the SQL standard. It's also supported by DB2, SAP HANA, Firebird, Teradata and several others (and Oracle supported that _ages_ before SQL Server or any other DBMS) – May 21 '16 at 10:25
That's correct. I have removed direct link to T-SQL in my answer. Thanks. – Alexei - check Codidact May 21 '16 at 10:41
Thanks for the suggestion. Still want to find out the solution using self-join as I am hoping to understand how self-join works in this scenario. Thank you anyway. – Dorisacat May 21 '16 at 11:12

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

2 Answers2