More MAX PartTran.TranDate problems in BAQ

[rturrentine (Rick Turrentine)]

Another new task, another pain. I’ve been asked to create a dashboard for Sales. They want the part number product group, last date the part was purchased or manufactured, the job number or PO number, extend costs and vendor, and a few other minor fields.
I created the BAQ following @kcrusen on this post, E10.0 - SubQuery return maximum transaction date (Bin Ex.)
I started with Part and added a sub-query for Purchase transaction using PartTran and filtering for PUR-STK. I linked the two queries and made a calculated filed in the top query for max(PURSTK.PartTran_TranDate).
This worked and I have all the parts listed with the last Purchased date listed next to purchased parts. As soon as I add another filed from my sub-query my BAQ blows up with multiple rows. It seems any iteration of adding another field blows up the BAQ.
Here’s my current Phrase build, I haven’t added the second sub-query for MFG part yet.

And here’s my query phrase:

/*
 * 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].[PartNum] as [Part_PartNum],
	[Part].[TypeCode] as [Part_TypeCode],
	[Part].[ProdCode] as [Part_ProdCode],
	[ProdGrp].[ProdGrup_Description] as [ProdGrup_Description],
	(max(PURSTK.PartTran_TranDate)) as [Calculated_MaxPurDate]
from Erp.Part as Part
left outer join  (select 
	[ProdGrup].[Company] as [ProdGrup_Company],
	[ProdGrup].[ProdCode] as [ProdGrup_ProdCode],
	[ProdGrup].[Description] as [ProdGrup_Description]
from Erp.ProdGrup as ProdGrup
group by [ProdGrup].[Company],
	[ProdGrup].[ProdCode],
	[ProdGrup].[Description])  as ProdGrp on 
	ProdGrp.ProdGrup_Company = Part.Company
	and ProdGrp.ProdGrup_ProdCode = Part.ProdCode
left outer join  (select 
	[PartTran].[Company] as [PartTran_Company],
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[TranQty] as [PartTran_TranQty],
	[PartTran].[UM] as [PartTran_UM],
	[PartTran].[ExtCost] as [PartTran_ExtCost],
	[PartTran].[PONum] as [PartTran_PONum],
	[PartTran].[POLine] as [PartTran_POLine],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[Vendor].[PhoneNum] as [Vendor_PhoneNum],
	[PartTran].[TranDate] as [PartTran_TranDate],
	[PartTran].[TranNum] as [PartTran_TranNum]
from Erp.PartTran as PartTran
inner join Erp.Vendor as Vendor on 
	PartTran.Company = Vendor.Company
	and PartTran.VendorNum = Vendor.VendorNum
where (PartTran.TranType = 'PUR-STK')
group by [PartTran].[Company],
	[PartTran].[PartNum],
	[PartTran].[TranQty],
	[PartTran].[UM],
	[PartTran].[ExtCost],
	[PartTran].[PONum],
	[PartTran].[POLine],
	[Vendor].[VendorID],
	[Vendor].[Name],
	[Vendor].[PhoneNum],
	[PartTran].[TranDate],
	[PartTran].[TranNum])  as PURSTK on 
	PURSTK.PartTran_Company = Part.Company
	and PURSTK.PartTran_PartNum = Part.PartNum
group by [Part].[PartNum],
	[Part].[TypeCode],
	[Part].[ProdCode],
	[ProdGrp].[ProdGrup_Description]
order by Part.PartNum  >```

[rturrentine (Rick Turrentine)]

To give full context of what Sales is asking and I’m planning to give. They had asked for a report of every part number (all 63,724) with the purchased or manufactured information including Part number, description, product group, last transaction date, PO and line number, job number (if MFG), trans quantity, extended costs, vendor and vendor address / phone number.
My plan was to build a dashboard for them to filter by product group and maybe type (P or M) so the dashboard didn’t crash from the size.
I intended to build the query with 2 sub-queries, one for PUR-STK transactions and another for MFG-STK. I would link them to the Part top level query so every part number was available and the last transaction for each type (P or M) would display with the relevant information.
Does this sound sane? It’s been a humbling week so far, I haven’t had much success with these projects and it’s truly showing me how little I understand of the workings inside Epicor.

[klincecum (Kevin Lincecum)]

@rturrentine

Does this sound sane? It’s been a humbling week so far, I haven’t had much success with these projects and it’s truly showing me how little I understand of the workings inside Epicor.

You’re on the right track.

[klincecum (Kevin Lincecum)]

I’m sure someone will beat me to it, and I hope they do, but I’ve basically built the same thing before.
Let me ponder and see if I have any tips.

[rturrentine (Rick Turrentine)]

Thanks. So for S&G I threw the entire BAQ together as I think it should go. It looks like my subqueries are returning every tran date instead of the last tran date.
Here’s my query:

This time I did my calculated field for MAX trandate on the subqueries instead of the top level query:

And my Query Phrase

 * 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].[PartNum] as [Part_PartNum],
	[Part].[TypeCode] as [Part_TypeCode],
	[Part].[ProdCode] as [Part_ProdCode],
	[ProdGrp].[ProdGrup_Description] as [ProdGrup_Description],
	[PURSTK].[Calculated_MaxPurDate] as [Calculated_MaxPurDate],
	[PURSTK].[PartTran_PONum] as [PartTran_PONum],
	[PURSTK].[PartTran_POLine] as [PartTran_POLine],
	[PURSTK].[PartTran_TranQty] as [PartTran_TranQty],
	[PURSTK].[PartTran_UM] as [PartTran_UM],
	[PURSTK].[PartTran_ExtCost] as [PartTran_ExtCost],
	[PURSTK].[Vendor_VendorID] as [Vendor_VendorID],
	[PURSTK].[Vendor_Name] as [Vendor_Name],
	[PURSTK].[Vendor_PhoneNum] as [Vendor_PhoneNum],
	[MFGSTK1].[Calculated_MaxMFGDate] as [Calculated_MaxMFGDate],
	[MFGSTK1].[MFGSTK_JobNum] as [MFGSTK_JobNum],
	[MFGSTK1].[MFGSTK_TranQty] as [MFGSTK_TranQty],
	[MFGSTK1].[MFGSTK_UM] as [MFGSTK_UM],
	[MFGSTK1].[MFGSTK_ExtCost] as [MFGSTK_ExtCost]
from Erp.Part as Part
left outer join  (select 
	[ProdGrup].[Company] as [ProdGrup_Company],
	[ProdGrup].[ProdCode] as [ProdGrup_ProdCode],
	[ProdGrup].[Description] as [ProdGrup_Description]
from Erp.ProdGrup as ProdGrup
group by [ProdGrup].[Company],
	[ProdGrup].[ProdCode],
	[ProdGrup].[Description])  as ProdGrp on 
	ProdGrp.ProdGrup_Company = Part.Company
	and ProdGrp.ProdGrup_ProdCode = Part.ProdCode
left outer join  (select 
	[PartTran].[Company] as [PartTran_Company],
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[TranQty] as [PartTran_TranQty],
	[PartTran].[UM] as [PartTran_UM],
	(max(PartTran.TranDate)) as [Calculated_MaxPurDate],
	[PartTran].[PONum] as [PartTran_PONum],
	[PartTran].[POLine] as [PartTran_POLine],
	[PartTran].[ExtCost] as [PartTran_ExtCost],
	[Vendor].[VendorID] as [Vendor_VendorID],
	[Vendor].[Name] as [Vendor_Name],
	[Vendor].[PhoneNum] as [Vendor_PhoneNum],
	[PartTran].[TranDate] as [PartTran_TranDate],
	[PartTran].[TranNum] as [PartTran_TranNum]
from Erp.PartTran as PartTran
inner join Erp.Vendor as Vendor on 
	PartTran.Company = Vendor.Company
	and PartTran.VendorNum = Vendor.VendorNum
where (PartTran.TranType = 'PUR-STK')
group by [PartTran].[Company],
	[PartTran].[PartNum],
	[PartTran].[TranQty],
	[PartTran].[UM],
	[PartTran].[PONum],
	[PartTran].[POLine],
	[PartTran].[ExtCost],
	[Vendor].[VendorID],
	[Vendor].[Name],
	[Vendor].[PhoneNum],
	[PartTran].[TranDate],
	[PartTran].[TranNum])  as PURSTK on 
	PURSTK.PartTran_Company = Part.Company
	and PURSTK.PartTran_PartNum = Part.PartNum
left outer join  (select 
	[MFGSTK].[Company] as [MFGSTK_Company],
	[MFGSTK].[PartNum] as [MFGSTK_PartNum],
	[MFGSTK].[TranType] as [MFGSTK_TranType],
	(max(MFGSTK.TranDate)) as [Calculated_MaxMFGDate],
	[MFGSTK].[TranQty] as [MFGSTK_TranQty],
	[MFGSTK].[UM] as [MFGSTK_UM],
	[MFGSTK].[JobNum] as [MFGSTK_JobNum],
	[MFGSTK].[ExtCost] as [MFGSTK_ExtCost],
	[MFGSTK].[SysDate] as [MFGSTK_SysDate],
	[MFGSTK].[TranNum] as [MFGSTK_TranNum]
from Erp.PartTran as MFGSTK
where (MFGSTK.TranType = 'MFG-STK')
group by [MFGSTK].[Company],
	[MFGSTK].[PartNum],
	[MFGSTK].[TranType],
	[MFGSTK].[TranQty],
	[MFGSTK].[UM],
	[MFGSTK].[JobNum],
	[MFGSTK].[ExtCost],
	[MFGSTK].[SysDate],
	[MFGSTK].[TranNum])  as MFGSTK1 on 
	MFGSTK1.MFGSTK_Company = Part.Company
	and MFGSTK1.MFGSTK_PartNum = Part.PartNum
group by [Part].[PartNum],
	[Part].[TypeCode],
	[Part].[ProdCode],
	[ProdGrp].[ProdGrup_Description],
	[PURSTK].[Calculated_MaxPurDate],
	[PURSTK].[PartTran_PONum],
	[PURSTK].[PartTran_POLine],
	[PURSTK].[PartTran_TranQty],
	[PURSTK].[PartTran_UM],
	[PURSTK].[PartTran_ExtCost],
	[PURSTK].[Vendor_VendorID],
	[PURSTK].[Vendor_Name],
	[PURSTK].[Vendor_PhoneNum],
	[MFGSTK1].[Calculated_MaxMFGDate],
	[MFGSTK1].[MFGSTK_JobNum],
	[MFGSTK1].[MFGSTK_TranQty],
	[MFGSTK1].[MFGSTK_UM],
	[MFGSTK1].[MFGSTK_ExtCost]
order by Part.PartNum>```

[rturrentine (Rick Turrentine)]

My results, a few of the 183,000 lines, 3X the number of parts.

The circled items are duplicates.

[klincecum (Kevin Lincecum)]

You need to do a max in your subqueries on the date, something like this:

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[ClassID] as [Part_ClassID],
	[Part].[TypeCode] as [Part_TypeCode],
	[SubQuery2].[PartTran_PartNum] as [PartTran_PartNum],
	[SubQuery2].[PartTran_TranType] as [PartTran_TranType],
	[SubQuery2].[PartTran_TranQty] as [PartTran_TranQty],
	[SubQuery2].[Calculated_lasttrandate] as [Calculated_lasttrandate]
from Erp.Part as Part
left outer join  (select 
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[TranType] as [PartTran_TranType],
	[PartTran].[TranQty] as [PartTran_TranQty],
	(max(PartTran.TranDate)) as [Calculated_lasttrandate]
from Erp.PartTran as PartTran
where (PartTran.TranType = 'MFG-STK')
group by [PartTran].[PartNum],
	[PartTran].[TranType],
	[PartTran].[TranQty])  as SubQuery2 on 
	Part.PartNum = SubQuery2.PartTran_PartNum

[klincecum (Kevin Lincecum)]

What I just posted will give you the last time a part was manufactured.
Then you can build off of that.

[klincecum (Kevin Lincecum)]

My query is not complete, I have duplicates, but I forgot what I need to filter on

[rturrentine (Rick Turrentine)]

Yes, When I replicate your query I get duplicates too. It’s OK, I think we’re close. Just have to eliminate the duplicates.

[klincecum (Kevin Lincecum)]

Do it on the last tranNum (max(PartTran.TranNum))

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[ClassID] as [Part_ClassID],
	[Part].[TypeCode] as [Part_TypeCode],
	[SubQuery2].[PartTran_TranType] as [PartTran_TranType],
	[SubQuery2].[PartTran_TranNum] as [PartTran_TranNum],
	[SubQuery2].[PartTran_TranDate] as [PartTran_TranDate],
	[SubQuery2].[Calculated_lasttrannum] as [Calculated_lasttrannum]
from Erp.Part as Part
left outer join  (select 
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[TranType] as [PartTran_TranType],
	[PartTran].[TranNum] as [PartTran_TranNum],
	[PartTran].[TranDate] as [PartTran_TranDate],
	(max(PartTran.TranNum)) as [Calculated_lasttrannum]
from Erp.PartTran as PartTran
where (PartTran.TranType = 'MFG-STK')
group by [PartTran].[PartNum],
	[PartTran].[TranType],
	[PartTran].[TranNum],
	[PartTran].[TranDate])  as SubQuery2 on 
	Part.PartNum = SubQuery2.PartTran_PartNum

[josecgomez]

That’s cause you are displaying Job Info can’ do that and grup.

[rturrentine (Rick Turrentine)]

Thanks. I’m a bit confused. Why can’t I display job info? Is it the same with the PO information?
So, I can show the last PartTran tran date but if I include any other information the BAQ starts duplicating rows. I can’t ungroup and of the fields, it keeps giving me errors if I do.
Do I need to create a subquery to filter to last tran date and another for the PO and Job information? I want to argue the logic of this but Epicor has often shown me that 24 divided by apples often equals bicycles.

[josecgomez]

You cannot show Max(date) and also Job Num , PO etc from part tran cause that’s not how SQL grouping works. SQL aggregate grouping groups like fields… jobs are all going to be different so you’ll get one row per job and or PO.

You can use some of the techniques that @Banderson outlined here in his presentation to do what you want like Windowing Function and Ranking.

BAQ Calculated fields insights presentation

Hey Everyone, This is a recording done after the fact of my insights presentation due to the technical difficulties I had during the presentation. Now I was able to record all of the live things that I wanted to show. I will apologize for my fat head in the way for some of it, but I thought it would be better if you could see me talking instead of just a disembodied voice. And I didn’t feel like taking the time to figure out how to use active presenter well enough to do some editing, so you ge…

[klincecum (Kevin Lincecum)]

Yes, you can display everything you want, but you’ll have to break some of it out in pieces to build off of.

Takes some thought.

[rturrentine (Rick Turrentine)]

Thank you for that explanation. I was never completely sure how grouping worked in BAQs. I’m studying the video this afternoon.

[hkeric.wci (Haso Keric)]

Also look into Sub-Selects
I usually end up doing something like this, if I need more columns.

More Info:

Using Count() in BAQ - Incorrect Syntax

@tmcmullen you can also do Sub-Select SubQuery. Then you wouldnt need to do GROUP BY on your Top-Query. In a Nutshell: Make a Sub-Query but DO NOT Use it on the Designer On your TopLevel Query add the Sub-Query to a Calculated Field On the Sub-Query under SubQuery Criteria you can add WHERE Clause. Example: SubQuery1: [image] [image] SubQuery2: [image] [image] Result: [image] [image] Basically in a simpler view it does SELECT (SELECT count(*) FROM table WHERe...) as YourCalculate…

[kananga (John K)]

@josecgomez

You can use some of the techniques that @Banderson outlined here in his presentation to do what you want like Windowing Function and Ranking.

Windowed functions are the way! So much easier to write and reason about and live with than group by.
For example, from the first few lines of your query, you could do this instead:

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[TypeCode] as [Part_TypeCode],
	[Part].[ProdCode] as [Part_ProdCode],
	[ProdGrp].[ProdGrup_Description] as [ProdGrup_Description],
	(max(PURSTK.PartTran_TranDate) over(partition by Part.PartNum) as [Calculated_MaxPurDate]
from Erp.Part as..........

Partition by sets the grouping for the max aggregate. Basically, we want all the max’s, partitioned by each distinct part number.

Since the only thing we care about here is the most recent purchase date for the part number, we only need to partition by the part number.

One nice example of why windowed functions are better is that you could add:

,(max(PURSTK.PartTran_TranDate) over(partition by Part.TypeCode) as [Calculated_TypeLastPurchased]
,(max(PURSTK.PartTran_TranDate) over(partition by Part.ProdCode) as [Calculated_ProdCodeLastPurchased]

Not that that’s really useful but it’s a nice example of how painless it is to get max or other aggregates over different groupings using windowed functions, and each field is aggregated in total isolation from other fields.

[rturrentine (Rick Turrentine)]

Wow did this work for me. Thank you very much. I watched the video from @Banderson (several times) and was able to build the queries that he did. Then I was able to move over and recreate my BAQ using Windows Functions and it worked perfectly. While your help was greatly needed I am marking that video as a solution for anyone in the future looking for guidance on BAQs.