How can I find all Sites with latest date?-CodePudding

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.

CodePudding user response：

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