Amy.G (4/8/2013)
I can't believe this. My query went from nearly 10 minutes to 2 seconds. I had this idea that the data had to be summed before joining. The dangers of tunnel vision, I guess.
I'm not an expert or anything, but I've seen plenty of evidence that the sql server software will rearrange query operations in a variety of ways as long as the results would be logically equivalent to what you spec'ed in your query text. Sometimes it can pay to just write your query in the most obvious way and see what the server does with it. Doesn't hurt to eyeball the plan also.
Good luck!