Anyone else wish Epicor used ‘EffectiveFromDate’ and ‘EffectiveToDate’, instead of just EffectiveDate on things like Price Lists and Supplier Price Lists
Ok its possible to get the current record using subqueries, but it is a pain.
Not sure what you are referencing as the SPL has an expires field.
I imagine you’re trying to get the most recent effective date? You can do a subquery and then sort and number the results. Pull the #1 result into your main query.
Calculated field in subquery
ROW_NUMBER() OVER(PARTITION BY VendPart2.VendorNum, VendPart2.PartNum ORDER BY VendPart2.EffectiveDate DESC)
1 Like