Hey Tom

Thanks for getting here. The Cte definitely works for one off queries. This model I am looking to fix however is for a DataWarehouse and I need some sort of a more permanent solution.

The DW feeds an SSAS cube and there are requirements to allow drilling at any given time from Director to Rollup so essentially i need all values calculated and preloaded.

From a modeling standpoint, my ideal situation (which I am currently trying to map out now) is to resolve the M:N relationship by 1,2,3 Normal forms and creating the model from there.

I posted this to see if anyone had any ideas specifially from a modeling standpoint.