This window function is taking up 84% of my Epicor 10 BAQ Query and timing it out. Does anyone know what may be causing the massive drain? There are a lot of parts in this system so its been slower processing times in general, but this is the bottleneck.
I have to find the most recent approved rev but if there are no approved revs on the part just the most recently Effective date. I later filter this by row 1. This then goes into a recursive BOM. I have a parameter for plant on the table and an effective date <= to today.
Row_number() over (partition by PartRev.Company, PartRev.PartNum, PartRev.Plant order by PartRev.Approved desc, PartRev.EffectiveDate desc)