I’m just now taking my first look into it, but pretty confident that this post and example BAQ has some significance to the query that we are discussing.
PS: I think between this and the work that @knash has provided, should be able to put something together that works as expected.
I am not certain just yet how to, or even if, the Calculated field used in the example BAQ can be modified to use the where clause as to control the Effective Date ( Approved Date ) as Ken shows. Still researching that part.
rank() over (partition by PartRev.PartNum Order By PartRev.EffectiveDate desc, PartRev.RevisionNum desc)
That’s the part that where the Rank is built. Maybe table criteria takes precedence, maybe there is easy way to edit the Calculation.