Here are two tables with relevant fields listed:
Invoices
InvoiceID
VendorID
InvoiceNumber
InvoiceDate
InvoiceTotal
Vendors
VendorID
VendorName
I’m trying to write a statement to return four columns showing VendorName, InvoiceNumber, InvoiceDate, and InvoiceTotal. I want to return one row per vendor showing each Vendor’s earliest Invoice. I have the following so far:
WITH FirstInvoice AS
(SELECT VendorID, MIN(InvoiceDate) AS EarliestInvoice
FROM Invoices
GROUP BY VendorID)
SELECT VendorName, EarliestInvoice
FROM Vendors JOIN FirstInvoice
ON Vendors.VendorID = FirstInvoice.VendorID
This shows the earliest invoice and the vendor name. However when I try to add the invoice dates and totals, it displays all invoices for each vendor.
Please help. Thank you.
