Grouping average lead time for purchased parts, multiple vendors

Kinetic ERP
,
1

Good afternoon, Using E10, Classic UX, and relatively new to BAQs. I am trying to work through average ‘real’ lead time for parts, but we sometimes utilize multiple vendors for the same parts, based on availability. I am getting all the data I need, but cannot decipher how to get the “Average” to show as just one line per Vendor/Part (grouped). I have tried a number of things, but either get syntactical error, or no modification to the results. Here is what I have so far:

‘’'/*

  • Disclaimer!!!
  • This is not a real query being executed, but a simplified version for general vision.
  • Executing it with any other tool may produce a different result.
    */

select
[Part].[ClassID] as [Part_ClassID],
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[Vendor].[VendorID] as [Vendor_VendorID],
[SubQuery2].[RcvDtl_VenPartNum] as [RcvDtl_VenPartNum],
[PODetail].[PONUM] as [PODetail_PONUM],
[PORel].[POLine] as [PORel_POLine],
[PORel].[PORelNum] as [PORel_PORelNum],
[POHeader].[OrderDate] as [POHeader_OrderDate],
[SubQuery2].[RcvDtl_ReceiptDate] as [RcvDtl_ReceiptDate],
[PODetail].[UnitCost] as [PODetail_UnitCost],
(datediff(day, POHeader.OrderDate, SubQuery2.RcvDtl_ReceiptDate)) as [Calculated_ActLeadTime],
(AVG(CAST(ActLeadTime AS DECIMAL(10,2))) over (PARTITION BY Part.PartNum, Vendor.VendorID)) as [Calculated_AvgLeadTime]
from Erp.Part as Part
left outer join Erp.PODetail as PODetail on
Part.Company = PODetail.Company
and Part.PartNum = PODetail.PartNum
inner join Erp.POHeader as POHeader on
POHeader.Company = PODetail.Company
and POHeader.PONum = PODetail.PONUM
inner join Erp.Vendor as Vendor on
POHeader.Company = Vendor.Company
and POHeader.VendorNum = Vendor.VendorNum
and ( not Vendor.VendorID = ‘HASKI001’ )

inner join Erp.PORel as PORel on
PODetail.Company = PORel.Company
and PODetail.PONUM = PORel.PONum
and PODetail.POLine = PORel.POLine
and ( PORel.PORelNum = 1 )

inner join (select
[RcvDtl].[PONum] as [RcvDtl_PONum],
[RcvDtl].[POLine] as [RcvDtl_POLine],
[RcvDtl].[PORelNum] as [RcvDtl_PORelNum],
[RcvDtl].[VenPartNum] as [RcvDtl_VenPartNum],
[RcvDtl].[ReceiptDate] as [RcvDtl_ReceiptDate],
[RcvDtl].[ReceivedComplete] as [RcvDtl_ReceivedComplete]
from Erp.RcvDtl as RcvDtl
where (RcvDtl.ReceiptDate >= dateadd (month, -6, Constants.Today) or RcvDtl.ReceiptDate = ‘Null’)) as SubQuery2 on
PORel.PONum = SubQuery2.RcvDtl_PONum
and PORel.POLine = SubQuery2.RcvDtl_POLine
and PORel.PORelNum = SubQuery2.RcvDtl_PORelNum
group by [Part].[ClassID],
[Part].[PartNum],
[Part].[PartDescription],
[Vendor].[VendorID],
[SubQuery2].[RcvDtl_VenPartNum],
[PODetail].[PONUM],
[PORel].[POLine],
[PORel].[PORelNum],
[POHeader].[OrderDate],
[SubQuery2].[RcvDtl_ReceiptDate],
[PODetail].[UnitCost]
order by Part.PartNum, Vendor.VendorID’‘’

Results:

image
image1159×131 6.05 KB

Fairly certain this is pretty easy, and I have just looked at so many post that I can’t see the forest for the trees.

Any help or guidance would be greatly appreciated.

2 Likes
2

It looks like your average is indeed calculating per Vendor/Part.
(9+10+12+7)/4 = 9.5
Are you saying you want the end results one line per vendor per part rather than showing each receiving detail?

3

Make a new Top level subquery that uses the current query as a source and group by the ClassED, Part, Desc, Supplier ID, Supplier Part and AvgLeadTime.

You’re going to get multi-rows per VendorID if you include PO, Order Date, etc.

1 Like
4

Yes, Josh, that is correct.

5

Randy, just to clarify, would I change the current toplevel query to an innersub query?

2 Likes
6

Then yes do as Randy said and yes change your current top to a sub. There are likely more efficient sql using CTEs but this works. Well done!

7

Thanks, Randy. That was the ticket.

1 Like
8

Thanks for the feedback Josh. I knew it would be something simple, just couldn’t get my brain in the right space to see it, lol.

1 Like