I'm fairly new to SQL and am trying to get the price for a product transaction on a particular date my looking up the most recent price of that product prior to the transaction within a price catalog.
Specifically, I have the two following tables:
Transactions Catalog
----------------------------------------------------------------------------
ProductID | Design | Transaction_DT ProductID | Price | Effective_DT
1 | Plaid | 5/14/2016 1 | 20 | 4/22/2016
2 | Solid | 3/26/2016 1 | 10 | 5/2/2016
3 | PolkaDot | 4/12/2016 1 | 5 | 5/15/2016
4 | Solid | 4/24/2016 2 | 50 | 3/22/2016
5 | PolkaDot | 2/24/2016 2 | 25 | 4/1/2016
6 | PinStripe | 3/29/2016 2 | 10 | 4/2/2016
3 | 30 | 4/5/2016
3 | 25 | 4/9/2016
3 | 22 | 4/12/2016
4 | 12 | 3/15/2016
4 | 8 | 3/27/2016
4 | 6 | 4/25/2016
5 | 15 | 2/23/2016
5 | 11 | 2/25/2016
5 | 6 | 2/28/2016
6 | 26 | 2/2/2016
6 | 17 | 3/19/2016
6 | 13 | 5/16/2016
I have entered the following code:
SELECT Transactions.ProductID,
Catalog.Price,
Transactions.Transaction_DT,
Transactions.Design
FROM Transactions
LEFT JOIN
Catalog ON Transactions.ProductID = Catalog.ProductID AND
Catalog.Effective_DT = (
SELECT MAX(Effective_DT)
FROM Catalog
WHERE Effective_DT <= Transactions.Transactions DT
)
And obtained the following output:
ProductID | Price | Transaction_DT | Design
1 | Null | 5/14/2016 | Plaid
2 | 50 | 3/26/2016 | Solid
3 | 22 | 4/12/2016 | PolkaDot
4 | Null | 4/24/2016 | Solid
5 | 15 | 2/24/2016 | PolkaDot
6 | Null | 3/29/2016 | PinStripe
I would like to return the Price for products 1, 4, and 6 to be 10, 8, and 17 respectively (in addition to the correct prices which were properly output) instead of the Null values I'm getting. Any ideas on how I can obtain the proper results?
You forgot to filter the correlated query by the productID. You are not getting the correct latest date for the product. You need to use this query:
SELECT Transactions.ProductID,
Catalog.Price,
Transactions.Transaction_DT,
Transactions.Design
FROM Transactions
LEFT JOIN
Catalog ON Transactions.ProductID = Catalog.ProductID AND
Catalog.Effective_DT = (
SELECT MAX(Effective_DT)
FROM Catalog
WHERE Effective_DT <= Transactions.Transactions_DT
and ProductID = Transactions.ProductID
)