Briceston (9/30/2013)
Ok, understood.I used your last code example in my environment, and it's taking long to execute. I keep having to cancel it at the 4min mark or else the DBA will send me a less than stellar email about the performance of my query. Any adjustments I can possible make within the code?
dwain.c (9/29/2013)
Briceston (9/29/2013)
First, thank you for replying. How are you deriving your average cost figures?I included the intermediate cost and members columns so you could see that. Each is the sum of the current record plus the prior 2. The average cost is then cost/members.
Can you post more info on your source table, specifically PRIMARY KEY and any available indexes?
Too bad you're not on SQL 2012 as that has a nifty solution for this problem.
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