Question

Locked

SQL 2005 Query Help

By powlboyjr ·
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.

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Hmm

by Tony Hopkinson In reply to SQL 2005 Query Help

Your basic problem is your base query is
Select VendorID, Min(InvoiceDate) from Invoices Group by VendorID, but you want to go back to the Invoices table. As soon as you add in invoice number, it goes in the group by and now you have minimum invoice date per invoice, which isn't a shed load of use....

Select VendorID,InvoiceNumber,EarliestInvoiceDate From Invoices,(Select VendorID, Min(InvoiceDate) as EarliestInvoiceDate From
Invoices GroupBy VendorID) innerq
Where Invoices.vendorID = innerQ.VendorID and Invoices.InvoiceDate = innerQ.EarliestInvoiceDate

should do the job, I'll leave the join with vendors to you.

NB don't forget this still could find more than one invoice per vendor, if they were the same earliest date.

Back to Web Development Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums