SQLSig (11/5/2013)
Admin, please move this post to the TSQL 2012 forum. I just realized that it was posted in 2K5.
Unfortunately, I don't believe this forum has an administrator that will move your post to the 2012 forum. Neither can you close a thread.
What you can do is to re-post to the SQL 2012 forum, and post a notice here to refer to that thread (use the url IFCode Shortcut on the left side of the posting window to post the URL).
If you do so, then you're going to need to help us (free) helpers out a bit by providing
- DDL for the tables you've presented
- Consumable sample data in the form of INSERTs into those tables
If the above matches the pictorial data you provided, the expected results are clear. So are the requirements. The only challenging requirement in the lot is the last one. And you may want to add some sample data and expected results to cover that one (I don't think it is covered in what you provided).
I think if you follow my suggestion someone will come along and give you a working solution pretty quickly.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St