SQL Server 2012 – Query Invoices with Earliest Date for Each Vendor

sql serversql-server-2012subquery

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?

Best Answer

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.

Related Question