Average Purchase Lead Time Calculation

Hello,

I am working on a simple BAQ to calculate the average lead time for purchased parts. (Receipt date - Order date). I am able to calculate the number of business days for each purchase transaction using PartTran and some clever formulas found on this forum. I was also able to calculate the count of distinct PO/Line/Rel there are for each part (using the dense_rank() function). Count POs:

dense_rank() over (partition by PartTran1.PartNum order by POLineRel) 
+ dense_rank() over (partition by PartTran1.PartNum order by POLineRel desc) 
- 1 -MAX(CASE WHEN POLineRel IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY PartTran1.PartNum)

I am not sure about how to get the average of the LeadTime. It appears to work if I use a windowed function that I have here:

AVG(CAST(LeadTime AS DECIMAL(10,2))) over (PARTITION BY PartTran1.PartNum )

Upon closer inspection though there are multiple records in PartTran for the same POLineRel. For example, if something was unreceived and then re-received for some reason. Since there are multiple records for the same PO/Line/Rel then it would be counted multiple times in the Avg calculation which may skew the results. How do I filter it to only take the average on the first occurrence of the POLineRel? In summary, what I want is Avg(LeadTime) Partition by (PartNum) with DISTINCT (POLineRel). Or is there a way to filter out multiple occurrences of the same POLineRel in PartTran?

Hi
Rather than use the PartTran table why not use the Receive Detail table to calculate the lead time - then you won’t get the issue with Receiving/Un-Receiving

1 Like

Thanks Sue! Not sure why I was trying to use PartTran anyway. That seems to work just fine!