We have a master table for Receipts which has
RCPSEQ, RCPNUMBER, PONUMBER, VENDORNUM, DATERCPT
AND Detail table which has:
RCPSEQ, PONUMBER, ITEM
I want to write a query to pick the Item number and the VendorNum who recently sold us that Item. Problem is grouping. with below query I am getting multiple PONUMBEr for same item and date.
SELECT p1.PONUMBER, P2.ITEMNO, (P2.MxDTAR) AS DATEM FROM dbo.PORCPL P1 join (select ITEMNO, max(DTARRIVAL)as MxDTAR from PORCPL group by ITEMNO) as P2 on P1.ITEMNO=p2.ITEMNO order by ITEMNO