BAQ return only 1 row per part number

Epicor ERP 10
3

The query below is also a good place to start. This will bring back the most recent entered transactions in the system by partnum.

You can always make the b table more to what you are looking for by adding the TranType criteria there.

Select a.PartNum, a.TranType, a.TranDate, a.TranQty, b._RowNum
            From Dbo.PartTran a
            Inner Join ( -- Use Left Join if the records missing from Table B are still required
                Select SysRowID, 
                        ROW_NUMBER() OVER (PARTITION BY partnum ORDER BY  SysRevID DESC ) As _RowNum
                from Dbo.PartTran                
                        ) b On b.SysRowID = a.SysRowID 
Where 1 =1 and b._RowNum = 1
1 Like