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?