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!
One way you can do this is:
with MostRecentInvoiced as (
select I.Customer, I.Item, I.Price as InvoicedPrice, I.TranDate as InvoicedDate
from Invoices I
where I.TranDate = (select max(TranDate) from Invoices I2
where I.Customer=I2.Customer
and I.Item=I2.Item)
)
, MostRecentQuoted as (
select Q.Customer, Q.Item, Q.Price as QuotedPrice, Q.TranDate as QuotedDate
from Quotes Q
where Q.TranDate = (select max(TranDate) from Quotes Q2
where Q.Customer=Q2.Customer
and Q.Item=Q2.Item)
)
select I.Customer, I.Item, I.InvoicedPrice, I.InvoicedDate, Q.QuotedPrice, Q.QuotedDate
from MostRecentInvoiced I
inner join
MostRecentQuoted Q
on Q.Customer=I.Customer
and Q.Item=I.Item
where I.InvoicedPrice <> Q.QuotedPrice
Here we prepare each set of 'most recent invoice', and 'most recent quote' for each customer and item separately; and then join them (internally SQLServer may not process them in this order but it helps to visualise).
My concerns for your script:
In relation to limiting results to just the 'Open' Quotes: if your intention is to exclude them from all consideration for this query; you need to add it to:
from Quotes Q2 where Q2.CloseDate is null
On the other hand, if you want to consider open & closed Quotes for the comparison, but simply discard the result rows if the quote is now closed, you can add it to only the main query.