Part Not Purchased in Last 12 months

ERP 10
1

Trying to write a report that shows us the parts that have not been purchased in the last 12 months. But I am having a rough time figuring out how to do those restrictions. Here’s what I have so far. I’m thinking to use TranDate to restrict it. But I would need to use an expression that says if the most recent transaction is in the last 12 months, exclude, otherwise include. If I’m off, or if you have a better idea, let me know. Open to all ideas and suggestions.

select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Part].[PUM] as [Part_PUM],
[Part].[IUM] as [Part_IUM],
[PartClass].[ClassID] as [PartClass_ClassID],
[PartClass].[Description] as [PartClass_Description],
[PartPlant].[PrimWhse] as [PartPlant_PrimWhse],
[PartPlant].[MinimumQty] as [PartPlant_MinimumQty],
[PartPlant].[MaximumQty] as [PartPlant_MaximumQty],
(sum(PartWhse.OnHandQty)) as [Calculated_OnHandQty],
(sum(PartWhse.DemandQty)) as [Calculated_DemandQty],
[Part].[CostMethod] as [Part_CostMethod],
[Part].[CreateDate_c] as [Part_CreateDate_c],
[PartCost].[AvgBurdenCost] as [PartCost_AvgBurdenCost],
[PartCost].[AvgLaborCost] as [PartCost_AvgLaborCost],
[PartCost].[AvgMaterialCost] as [PartCost_AvgMaterialCost],
[PartCost].[AvgMtlBurCost] as [PartCost_AvgMtlBurCost],
[PartCost].[AvgSubContCost] as [PartCost_AvgSubContCost],
[PartTran].[TranQty] as [PartTran_TranQty],
[PartTran].[TranNum] as [PartTran_TranNum],
[PartTran].[TranDate] as [PartTran_TranDate],
[PartTran].[TranType] as [PartTran_TranType]
from Erp.Part as Part
left outer join Erp.PartCost as PartCost on
Part.Company = PartCost.Company
And
Part.PartNum = PartCost.PartNum

left outer join Erp.PartPlant as PartPlant on
Part.Company = PartPlant.Company
And
Part.PartNum = PartPlant.PartNum

left outer join Erp.PartClass as PartClass on
Part.Company = PartClass.Company
And
Part.ClassID = PartClass.ClassID

left outer join Erp.PartWhse as PartWhse on
Part.Company = PartWhse.Company
And
Part.PartNum = PartWhse.PartNum

inner join Erp.PartTran as PartTran on
Part.Company = PartTran.Company
And
Part.PartNum = PartTran.PartNum
and ( PartTran.TranType like ‘STK-’ and PartTran.TranDate = )

where (Part.TypeCode = ‘P’ and Part.InActive = FALSE and Part.NonStock = FALSE and Part.RunOut = false)
group by [Part].[PartNum],
[Part].[PartDescription],
[Part].[PUM],
[Part].[IUM],
[PartClass].[ClassID],
[PartClass].[Description],
[PartPlant].[PrimWhse],
[PartPlant].[MinimumQty],
[PartPlant].[MaximumQty],
[Part].[CostMethod],
[Part].[CreateDate_c],
[PartCost].[AvgBurdenCost],
[PartCost].[AvgLaborCost],
[PartCost].[AvgMaterialCost],
[PartCost].[AvgMtlBurCost],
[PartCost].[AvgSubContCost],
[PartTran].[TranQty],
[PartTran].[TranNum],
[PartTran].[TranDate],
[PartTran].[TranType]

1 Like
2

Do a subquery to look at all parts with the max(date) on the transaction greater than your target date (12 months) then go a level up and join it to the part table with a join to included all from the part table. Then you can filter by the date column because the parts that don’t have the transaction will have a null (or empty string?) in that column. Those are the parts that you haven’t sold in 12 months.

1 Like
3

to build on what Brandon said.

Do you care about the last date? If not write a subquery on parttran.

select partnum
from Erp.PartTran
where cast(TranDate AS DATE) >= cast(DATEADD(year, -1, GETDATE()) AS DATE)
Group by partnum

Now create a main query and left join Part Table to the subquery. Create a custom field that evaluates the right side of the join. If there is a value then the part had a transaction in the past year.

Or add a criteria to only show items that return null.

You have a few options here.

Now you have your part list. Now you can build out what else you want to display from there.

1 Like
4

Separately, you may get some false positives using PartTran. If you just placed an order on a part and there has been no receipts yet then it will appear that it hasn’t been ordered yet when in fact it has.

You can build a sub-query on PODetail using similar logic from Brandon and Ken to select the PODetail records instead of PartTran records.

Just a thought,

Mark W.

2 Likes