BAQ - Part History Help

I am trying to write a BAQ that will allow me to pull the following information:

Part Number - Description - Class - Last Transaction Date

Part Number + Last Transaction Data ( Last time we used this part ) Last PUR-STK date or Last MTL-STK for Purchased and Last MFG-STK date for Manufactured

I have written this:

select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[PartTran].[TranType] as [PartTran_TranType],
[PartTran].[TranDate] as [PartTran_TranDate],
((max( PartTran.SysDate ))) as [Calculated_LastPurStk],
((max( PartTran.TranDate ))) as [Calculated_LastPurStkTran],
[Part].[ClassID] as [Part_ClassID]
from Erp.Part as Part
inner join Erp.PartTran as PartTran on
Part.Company = PartTran.Company
and Part.PartNum = PartTran.PartNum
and ( PartTran.TranType = ‘PUR-STK’ )

where (Part.PartNum > @PartNum)
group by [Part].[PartNum],
[Part].[PartDescription],
[PartTran].[TranType],
[PartTran].[TranDate],
[Part].[ClassID]

But this is not giving me the MTL Stk and the transaction dates are not lining up.

You are specifically filtering for only ‘PUR-STK’ transactions so you aren’t doing to get MTL-STK transactions.

image