I have two tables I would like to query and compare. I want to find the most recent price a customer has paid and compare it to the most recent price they were quoted for the same item but only returns results if the two prices are different. Below are some mock tables.
Table 1 (Invoices)
| Customer | Item | Price | TranDate |
|---|---|---|---|
| 1505 | ABC | 3.04 | 2024-5-17 |
| 2106 | ABC | 5.20 | 2024-4-01 |
| 1505 | ABC | 2.96 | 2024-1-16 |
| 1505 | ABC | 3.15 | 2023-12-15 |
Table 2 (Quotes)
| Customer | Item | Price | TranDate |
|---|---|---|---|
| 1505 | ABC | 2.96 | 2024-7-29 |
| 1505 | ABC | 3.08 | 2024-6-15 |
I can produce the individual queries that show the results similar to Table1 and Table2 above. It is the combining and most recent values that I am struggling with.
Results
| Customer | Item | InvPrice | QuotePrice |
|---|---|---|---|
| 1505 | ABC | 3.04 | 2.96 |
Script that I’ve tried.
SELECT Invoices.Customer, Invoices.Item, Invoices.Price AS [InvPrice], Quotes.Price AS [QuotePrice]
FROM Invoices
JOIN Quotes ON Quotes.Customer = Invoices.Customer
WHERE Quotes.Price <> Invoices.Price
AND Invoices.TranDate = (SELECT MAX(TranDate) FROM Invoices)
ORDER BY Customer
Thank you!
New contributor
Scott is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.