Get latest PartRev in a BAQ

[aarong (Aaron Gulley)]

Hello all,

Has anyone had any experience with PartRev I would like to say the latest PartRev in a BAQ at the moment I am pulling all of them back e.g if we have A B C it’s displaying them all when the latest one is C but the end user doesn’t want to enter a date.

Thank you
Aaron.

[bderuvo (Bryan DeRuvo)]

You can use a subquery in the baq to return the Max value using effective date or revision number.

@ckrusen covered it pretty well here…

E10.0 - SubQuery return maximum transaction date (Bin Ex.)

Here is a simple BAQ that retruns the MAX(PartTran.TranDate) for each part and TranType SubQuery1 (obviously it was completed after Subquery2 was made) [image] Subquery2 (it could have more tables and joins) [image] Subquery2 display fields. Note that all but the calculated field are “Group By”. The expression for the calculated field is MAX(PartTran.TranDate) [image] Subquery1’s table relations [image] Subquery1’s display fields. Note that none are group by. [image] And the resu…

[hkeric.wci (Haso Keric)]

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…

[Hogardy (Pierre Hogue)]

I use a Subquery with a calculated field of type int, I call Rank:

ROW_NUMBER() OVER (PARTITION BY PartRev.Company, PartRev.PartNum ORDER BY PartRev.EffectiveDate DESC)
I specify on PartRev the folowing criteria: EffectiveDate <= Today (because some may be for future use)
and AltMethod = “”; (otherwise I may have two records …)

When I add this subquery to the top query, I specify Rank = 1 I will get the latest rev.

Pierre

[aarong (Aaron Gulley)]

Hi Hogardy,

Can you show me a screen shot of your BAQ?

Thanks
Aaron.

[aarong (Aaron Gulley)]

Anyone able to have a look at this BAQ for me?

TEAGLE-LastPartRevision.baq (22.8 KB)

[Hogardy (Pierre Hogue)]

Summary

select 
	[Part].[PartNum] as [Part_PartNum],
	[Part].[PartDescription] as [Part_PartDescription],
	[SubQuery2].[PartRev_RevisionNum] as [PartRev_RevisionNum],
	[SubQuery2].[PartRev_RevDescription] as [PartRev_RevDescription],
	[SubQuery2].[PartRev_EffectiveDate] as [PartRev_EffectiveDate],
	[SubQuery2].[Calculated_RANK] as [Calculated_RANK],
	[SubQuery2].[PartRev_AltMethod] as [PartRev_AltMethod]
from Erp.Part as Part
left outer join  (select 
	[PartRev].[Company] as [PartRev_Company],
	[PartRev].[PartNum] as [PartRev_PartNum],
	[PartRev].[RevisionNum] as [PartRev_RevisionNum],
	[PartRev].[RevDescription] as [PartRev_RevDescription],
	[PartRev].[EffectiveDate] as [PartRev_EffectiveDate],
	(ROW_NUMBER() OVER (PARTITION BY PartRev.Company, PartRev.PartNum ORDER BY PartRev.EffectiveDate DESC)) as [Calculated_RANK],
	[PartRev].[AltMethod] as [PartRev_AltMethod]
from Erp.PartRev as PartRev
where (PartRev.AltMethod = '""'  and PartRev.EffectiveDate <= @Today))  as SubQuery2 on 
	Part.Company = SubQuery2.PartRev_Company
	and Part.PartNum = SubQuery2.PartRev_PartNum
	and ( SubQuery2.Calculated_RANK = 1  )

where (Part.PartNum = '10-100')

example latest part rev…pdf (33.1 KB)

[aarong (Aaron Gulley)]

That’s create but it’s still confusing…

I’ve got this far

Summary

select
[Part].[PartNum] as [Part_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
[LastRevision].[PartRev_RevisionNum] as [PartRev_RevisionNum],
[LastRevision].[PartRev_PartNum] as [PartRev_PartNum],
[LastRevision].[PartRev_Company] as [PartRev_Company],
[LastRevision].[Calculated_RANK] as [Calculated_RANK]
from Erp.Part as Part
left outer join (select
[PartRev].[Company] as [PartRev_Company],
[PartRev].[PartNum] as [PartRev_PartNum],
[PartRev].[RevisionNum] as [PartRev_RevisionNum],
[PartRev].[EffectiveDate] as [PartRev_EffectiveDate],
((ROW_NUMBER() OVER (PARTITION BY PartRev.Company, PartRev.PartNum ORDER BY PartRev.EffectiveDate DESC))) as [Calculated_RANK]
from Erp.PartRev as PartRev
where (PartRev.AltMethod = ‘0’ and PartRev.EffectiveDate <= @Today)) as LastRevision on
Part.Company = LastRevision.PartRev_Company
and Part.PartNum = LastRevision.PartRev_PartNum

[Hogardy (Pierre Hogue)]

What is confusing? … looks ok to me as long as Altmethod = ‘0’ is valid for you.
To test, force part.partnum = a part that you know has multiple revisions. You shall see all the revisions in your result (as I showed in my example, 3 revisions having different effective dates ).

Then you can add, on the added subquery LastRevision, the constraint Calculated_Rank=1 to get only the top revision record…

[hkeric.wci (Haso Keric)]

Attached
TEAGLE-LastPartRevision.baq (31.6 KB)

You prob should maybe change the joins to Left Join so you could show blanks

---

Thats the method I linked you above in my previous post:

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…