0

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?

3 Answers 3

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.

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

1
  • I don't think the ASC/DESC functionality can be used in this way in T-SQL. You need the subquery to return a single records, and it will fail if it returns multiple records. You'd need to do "SELECT TOP 1 ... ORDER BY InvoiceDate ASC"
    – SQLDevDBA
    Sep 1, 2016 at 14:11
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.