Select Most Recent Invoice Price for Customer versus Quote Price

Question

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!

Answer 1

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:

1. SELECT MAX(TranDate) subquery gets the latest invoice date regardless of which customer or which item it is,
2. your join also doesn't care if pairing up rows with the same item.

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:

1. The main query (the one at the bottom)
2. The Quotes CTE main part 'From Quotes Q where Q.CloseDate is null`
3. The subquery in the Quotes CTE: 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.