Jeff Moden (3/19/2012)
This is another contest problem where the best solution wouldn't likely be an "all in one" query.
I suggest the schema should be redesigned and this code would be part of a one-time fix to populate new tables rather than part of ongoing reporting.
I know... this is a vendor's application, entrenched business, just get it done "for today", and a dozen other reasons why we all still have jobs dealing with other people's implementation choices. 🙂