CODEWITHSUNDEEP
sql-server
Sparrowhawk
Sparrowhawk

Reputation: 394

SQL query to get records where first related record does not match

I have 2 tables, something like this:

Leases
ID, LeaseDate

LeaseInvoices
ID, LeaseID, InvoiceDate, StartDate

How can I find all cases where LeaseDate does not match InvoiceDate of the invoice with the first StartDate?

I can't work out how to do it, something along these lines?

SELECT * FROM LeaseInvoice
INNER JOIN Leases ON Leases.ID = LeaseInvoices.LeaseID
WHERE LeaseDate IS NULL
OR LeaseDate <> (
    SELECT TOP 1 InvoiceDate FROM LeaseInvoices
    ORDER BY StartDate DESC
)

Upvotes: 0

Views: 52

Answers (1)

Chris Mack
Chris Mack

Reputation: 5208

You could use the ROW_NUMBER() function for this, e.g.:

SELECT
    ID
    , LeaseDate
    , InvoiceDate
    , StartDate
FROM
    (
        SELECT
            L.ID
            , L.LeaseDate
            , I.InvoiceDate
            , I.StartDate
            , ROW_NUMBER() OVER (PARTITION BY L.ID ORDER BY I.StartDate) R
        FROM
            Leases L
            JOIN LeaseInvoices I ON L.ID = I.LeaseID
    ) Q
WHERE
    R = 1
    AND LeaseDate <> InvoiceDate

Upvotes: 1

Related Questions