Getting Max Date with Join Statement
-
I am trying to get the max PRM_StartDate from the query below but unsure how to format the query. Can anyone lend me some assistance?
SELECT IMA_ItemID, IMA_Price,PRD_UnitSalesPrice,PRM_StartDate FROM Item
LEFT JOIN PriceMatrix ON PRM_IMA_RecordID = IMA_RecordID
INNER JOIN PriceMatrixDetail ON PRD_PRM_RecordID = PRM_RecordID
WHERE IMA_ItemStatusCode = 'Active'
WHERE PRM_StartDate = (SELECT MAX(PRM_StartDate) FROM PriceMatrix)
Thank you.
-
You have two WHERE statements which is an issue.
You could do something like this unless you need it as a view.
DECLARE @somedate DATE
SET @somedate = (SELECT MAX(PRM_StartDate) FROM PriceMatrix)
...
WHERE PRM_StartDate = @somedate AND IMA_ItemStatusCode = 'Active'
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Your example using
WHERE PRM_StartDate = (SELECT MAX(PRM_StartDate) FROM PriceMatrix)
would , fixing the double WHERE, only return rows where PRM_StartDate is the maximum where IMA_ItemStatusCode = 'Active'.
So you would only get IMA_ItemID, IMA_Price,PRD_UnitSalesPrice,PRM_StartDate where PRM_StartDate is the maximum.
Is that your goal, or are you trying to get the maximum PRM_StartDate for each combination of IMA_ItemID, IMA_Price,PRD_UnitSalesPrice?
If the latter your query would look more like
SELECT IMA_ItemID, IMA_Price, PRD_UnitSalesPrice, MAX(PRM_StartDate) AS PRM_StartDateMax
FROM Item
LEFT JOIN PriceMatrix ON PRM_IMA_RecordID = IMA_RecordID
INNER JOIN PriceMatrixDetail ON PRD_PRM_RecordID = PRM_RecordID
WHERE IMA_ItemStatusCode = 'Active'
GROUP BY IMA_ItemID, IMA_Price,PRD_UnitSalesPrice; -
I wondered about something similar ratbak but since there wasn't any additional information I just stated the obvious issue. Did they want all 'Active' items amongst those that shared the max date OR get the max date from those with 'Active' records? Obviously, those are two very different things. It's hard to tell without sample data and expected results
SELECT quote FROM brain WHERE original = 1
0 rows returned -
My end goal is to get the maximum PRM_StartDate for each Item ID.
For example:
SELECT IMA_ItemID, IMA_Price,PRD_UnitSalesPrice,PRM_StartDate FROM Item
LEFT JOIN PriceMatrix ON PRM_IMA_RecordID = IMA_RecordID
INNER JOIN PriceMatrixDetail ON PRD_PRM_RecordID = PRM_RecordID
WHERE IMA_ItemStatusCode = 'Active' AND IMA_ItemID = 'X222-1'
Returns:
But I just want the one record that has 9/30/2022 (the max PRM_StartDate)
Thank you for your feedback. Very much appreciated.
-
Thanks for the clarification. Are you essentially looking for the most recent price of an item? I'm just wondering because you seem to have different sales prices for the same item on the same date. What would you expect in the case where the max date for an item had multiple values? Or do you simply want all the item prices for the maximum date?
Sorry about the edits, I keep getting interrupted so my mind is all over the place right now. Use ratbak's code above but exclude the prices if you only care about the max date for each item. I'm only asking about the prices because I assumed you joined that table for a reason.
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Yes I basically want the most recent price or last sales price, which would be the Max Start Date.
Thank you.
-
Try something like this but be mindful that you can still get multiple prices if the most recent date has more than one row per item.
SELECT
x.IMA_ItemID,
x.IMA_Price,
x.PRD_UnitSalesPrice,
x.PRM_StartDate
FROM
(
SELECT
IMA_ItemID,
IMA_Price,
PRD_UnitSalesPrice,
PRM_StartDate,
RANK() OVER (PARTITION BY IMA_ItemID ORDER BY PRM_StartDate DESC) AS RN
FROM
Item
LEFT JOIN PriceMatrix ON PRM_IMA_RecordID = IMA_RecordID
INNER JOIN PriceMatrixDetail ON PRD_PRM_RecordID = PRM_RecordID
WHERE
IMA_ItemStatusCode = 'Active'
) x
WHERE
x.RN = 1
SELECT quote FROM brain WHERE original = 1
0 rows returned -
Thank you this worked out great!
-
You're welcome.
One final tip before I forget is you should alias your table names. This will make it much easier to tell which columns belong to which tables. It will also prevent you from getting an error if you have multiple tables with the same column names.
i.e.
SELECT
i.IMA_ItemID,
i.IMA_Price,
pmd.PRD_UnitSalesPrice,
pm.PRM_StartDate,
RANK() OVER (PARTITION BY i.IMA_ItemID ORDER BY pm.PRM_StartDate DESC) AS RN
FROM
Item i
LEFT JOIN PriceMatrix pm ON pm.PRM_IMA_RecordID = i.IMA_RecordID
INNER JOIN PriceMatrixDetail pmd ON pmd.PRD_PRM_RecordID = pm.PRM_RecordID
WHERE
i.IMA_ItemStatusCode = 'Active'
SELECT quote FROM brain WHERE original = 1
0 rows returned -
This is a great tip and I will use this in the future for sure! Thank you!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply