Select last sales order date by item and company branch-CodePudding

I have two tables, one with item information and one with sales. I am trying to pull the last sales order date by item and branch.

T1 ItemBranch: ItemNumber BranchPlant StockingType

T2 SalesDetail: SalesOrderNumber ItemNumber OrderDate SalesOrderType BranchPlant

I need to query for StockingType = S and SalesOrderType = SO or SD

The same item can be sold at multiple branches and I want the most recent order date of each item by branch.

ItemNumber  BranchPlant    StockingType 
33202332      649900             S
33202332      653003             S  
33202332      644555             S
14590033      649900             S



T2 SalesDetail

SalesOrderNumber ItemNumber    OrderDate     SalesOrderType   BranchPlant
16590             14590033      1/23/2020        SO              649900
17950             14590033      10/21/2021       SO              649900
17806             33202332      2/24/2021        SO              653003
18900             33202332      6/23/2021        SO              653003
18920             33202332      7/29/2016        SO              644555             

Desired Result:

ItemNumber ItemBranch OrderDate
14590033      649900      10/21/2021
33202332      644555      7/29/2016      
33202332      653003      6/23/2021

CodePudding user response：

You can have the desired result using this query

SELECT t2.itemnum, t2.branchplant, MAX(t2.orderdate)
FROM t2
GROUP BY t2.itemnum, t2.branchplant;