tomek tomek (10/17/2013)
Hi All,First of all - Big Thanks for all your input (and good words for newbie as well:).
@Dwain.C
1. quirky update - I've seen it before. I didn't know the name. it's a great article you've sent me. Especially why one should use maxdop(1) and tablockx.
Long live the Quirky Update, long may it reign! To see its performance characteristic on another classic problem check here:Calculating Values within a Rolling Window in Transact SQL[/url].
tomek tomek (10/17/2013)
2. full outer join vs union all - yep. I've just noticed it. It could also happen that the same status type could change more than one time during a day. But I gues I would have to filter those somehow first...
Because you're using a DATETIME, the FULL JOIN will still work for this case. You will simply get multiple records for the same day with different times in your output results.
tomek tomek (10/17/2013)
btw. out of 4 forums I've sent the same request, this is were I got the best responses.Thanks again.
SSC rocks! Tell the world and drive them all to this site!
Now, as to your additional question in your post that follows this one. I don't understand the expected results you want. You mention PIVOT but I'm not sure what you want to PIVOT.
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