We manufacture equipment most of which we “make direct” to order. I am being asked to put together a report that shows our cost 12 months ago compared to now. We are average cost but what happens is that because we very seldom bring any on hand, our average costs are often not representative of the “expected cost” to produce a new machine. I’m curious to hear how others would approach this. I’m hoping there’s a simple way to achieve this. I’ve got subqueries and more subqueries on PartTran and trying to get transactions from a year ago but also trying to get the most recent MFG-CUS transaction. And I keep timing out. So looking for some community feedback. Hopefully y’all point me in the right direction.
1 Like
Maybe try summarizing PartTran MFG-CUS records by part and period…and grab min/max of the part cost?
EDIT - I did mine by period (yyyymm) - you may just want YYYY instead…
select
[PartTran].[Company] as [PartTran_Company],
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranType] as [PartTran_TranType],
((100*Year(TranDate))+Month(TranDate)) as [Calculated_TranDateBucket],
(MIN(MtlUnitCost+LbrUnitCost+BurUnitCost+SubUnitCost+MtlBurUnitCost)) as [Calculated_MinCost],
(MAX(MtlUnitCost+LbrUnitCost+BurUnitCost+SubUnitCost+MtlBurUnitCost)) as [Calculated_MaxCost]
from Erp.PartTran as [PartTran]
where (PartTran.TranType = ‘MFG-CUS’)
group by
[PartTran].[Company],
[PartTran].[PartNum],
[PartTran].[TranType],
((100*Year(TranDate))+Month(TranDate))
1 Like
I just recently did that 100 * Fiscal Year + Fiscal Month trick. I thought I was so clever. Haha I guess it’s reassuring to know that others think the same way (so I must not be crazy). So I like what you’re thinking. Essentially, stop trying to get every last transaction and just group them into monthly buckets. Then we can use last year’s same month bucket for last year’s cost
LOL…I’ve used that in Access for years so it extended well to BAQs too. 
I figured the min/max option would give you a cost range for the period in question…whether you do it at YearMo or Year (or maybe YearQtr?) is up to you…
1 Like
We’re in business. Grouping them into monthly buckets opened the door to get it to be efficient enough to complete before it timed out. Thank you!
Niiiiiice…glad it worked for ya.