I am trying to add all areas of data that will show all latest pricing. Because there are two latest dates for specific locations, only 5/6 show up with a date of 5/27. I need one location to show up as well as the latest date for it is 5/26. EXAMPLE BELOW:
ITEMNUMBER PRICETYPE FROMDATE SITE PRICECREATEDDATE PRICE PRICEQUANTITY 12345 cost 2022-05-26 A 2022-05-26 1.23 1 12345 cost 2022-05-27 G 2022-05-27 1.23 1 12345 cost 2022-05-27 M 2022-05-27 1.23 1 12345 cost 2022-05-27 N 2022-05-27 1.23 1 12345 cost 2022-05-27 P 2022-05-27 1.23 1 12345 cost 2022-05-27 U 2022-05-27 1.23 1
I currently have this query:
SELECT T.ITEMNUMBER, T.PRICETYPE, T.FROMDATE, T.SITE, T.PRICECREATEDDATE, T.PRICE, T.PRICEQUANTITY
FROM DBO.[D365 STRD Costs] T
INNER JOIN (
SELECT ITEMNUMBER, MAX(FROMDATE) AS MAXDATE
FROM DBO.[D365 STRD Costs]
GROUP BY ITEMNUMBER
) TM ON T.ITEMNUMBER = TM.ITEMNUMBER AND T.FROMDATE = TM.MAXDATE
RESULTS:
ITEMNUMBER PRICETYPE FROMDATE SITE PRICECREATEDDATE PRICE PRICEQUANTITY 12345 cost 2022-05-27 G 2022-05-27 1.23 1 12345 cost 2022-05-27 M 2022-05-27 1.23 1 12345 cost 2022-05-27 N 2022-05-27 1.23 1 12345 cost 2022-05-27 P 2022-05-27 1.23 1 12345 cost 2022-05-27 U 2022-05-27 1.23 1
I need SITE “A” to pull up as well since that is the latest price. It was just imported a day before.
Advertisement
Answer
You seem to be using SQL Server (or Sybase) instead of MySQL.
Try adding SITE to the GROUP BY if you need that to be included:
SELECT T.ITEMNUMBER, T.PRICETYPE, T.FROMDATE, T.SITE, T.PRICECREATEDDATE, T.PRICE, T.PRICEQUANTITY
FROM DBO.[D365 STRD Costs] t
INNER JOIN (
SELECT ITEMNUMBER, SITE, MAX(FROMDATE) AS MAXDATE
FROM DBO.[D365 STRD Costs]
GROUP BY ITEMNUMBER, SITE
) TM ON T.ITEMNUMBER = TM.ITEMNUMBER AND T.FROMDATE = TM.MAXDATE
AND T.SITE = TM.SITE