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.