DavidDroog (7/23/2015)

---

Hi All

Surprised this thread was still going when logged in this morning. ChrisM - I didn't realise people were actually offering to optimise my query, but if that is the case I gratefully accept for 2 reasons.

1. As a developer I am always on the look-out for better ways of doing things and would be very happy if I was shown the light.

2. I am somewhat cynical that some of more idealistic statements can actually survive the real world (eg no dynamic sql, no temp tables). I know my code has been universally bagged, but I actually think it is pretty good and it performs a complex function accurately and reasonably quickly. I therefore would like to issue a challenge to anyone who believes they can improve on it to submit their code here (by improve I essentially mean speed which is quantifiable - changing join types etc with no performance improvement is pointless as far as I am concerned).

If anyone is willing to take the challenge I will post a database with some sample data (I assume I can attach a bak file here).

Now the code I submitted before was just the output from a subquery for the full proc, so if you think that was complex wait until you see the full thing which is posted below, however I am posting a requirement description so you do not have to follow my code at all, just re-produce the results from my beast. I should also state this is a real world proc in development, so has not gone through testing yet, but I do not anticipate it to change much (unless as stated someone shows me the light!)

So who is up for the challenge? - I will quite happily eat humble pie if my cynicism is proven false.

...