SQL Server 2012 Query: Invoices with Earliest Date in State for each Vendor

Question

I have the tables:

1. Invoices ( VendorID, InvoiceDate, InvoiceNumber, InvoiceTotal,...),
2. Vendors (VendorName, VendorID,....)

I am trying to obtain a list of the earliest invoices for each vendor, together with VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal (for that earliest date).

Here is what I have :

SELECT VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal 
FROM Vendors V JOIN Invoices I ON V.VendorID=I.VendorID
WHERE  InvoiceDate   <= ( SELECT  Min(InvoiceDate) 
                          FROM Invoices 
                          JOIN Vendors ON V.VendorID=Vendors.VendorID  )
GROUP BY VendorName, InvoiceNumber, InvoiceDate, InvoiceTotal

The problem is that I am getting only one invoice, which is the earliest of all invoices, not the earliest by state. What is going on?

Answer 1

Looks like you don't have InvoiceState anywhere in your query.

Here it is with a little better formatting (PRE-COLUMN COMMA MASTERRACE!!)

SELECT VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal 
FROM Vendors V 
JOIN Invoices I 
    ON V.VendorID=I.VendorID 
WHERE InvoiceDate <= ( SELECT Min(InvoiceDate) 
                       FROM Invoices 
                       JOIN Vendors 
                       ON V.VendorID=Vendors.VendorID ) 
GROUP BY VendorName
, InvoiceNumber
, InvoiceDate
, InvoiceTotal

Adding it (InvoiceState) to your SELECT and GROUP BY would add that level of detail. I'd assume you need to add it as your first column (before VendorName).

Edit: looking at it further, seems like you'd also need it in your Subquery.

Answer 2

SELECT VendorName

, InvoiceNumber

, InvoiceDate

, InvoiceTotal 

FROM Vendors V 

JOIN Invoices I 

ON V.VendorID=I.VendorID 

WHERE InvoiceDate <= ( SELECT ASC(InvoiceDate) 

                   FROM Invoices 

                   JOIN Vendors 

                   ON V.VendorID=Vendors.VendorID ) 

GROUP BY VendorName

, InvoiceNumber

, InvoiceDate

, InvoiceTotal

In asending order you can get your earliest invoices for each vendor

Answer 3

Based on your sample, I am guessing you want one row per vendor and not by state (which appears nowhere in your example ;)).

Cross Apply Top 1 is my preferred method when dealing with such a need. This will be optimized as an INNER JOIN by the engine.

Also much more readable than squeezing a sub-query in the where clause, which has a big chance of needing an evaluation for each row.

SELECT
    VendorName
    , InvoiceNumber
    , InvoiceDate
    , InvoiceTotal 
FROM
    Vendors V
    CROSS APPLY (
        SELECT TOP 1 
            InvoiceNumber, 
            InvoiceDate, 
            InvoiceTotal
        FROM 
            Invoices Inv
        WHERE
            Inv.VendorId = V.VendorId
        ORDER BY
            InvoiceDate ASC
    ) I