>>>>This monstrous query is very hard to read and impossible to understand. Can you break it to managable pieces using either CTE or temp tables?
>>>
>>>Hi Naomi - as I said my TSql is very limited - don't know what CTE is and how would temp tables help ? - thanks
>>
>>Hi Pete
>>
>>if I was attempting to improve the performance of the query I'd first remove all the sub queries in it. See how the basic query performs and how it can be improved . then run the sub queries on their own and do the same for them then put it all back together.
>
>Hi Nicholas, if I remove the sub queries the performance improves dramatically but I don't know of a way to bolt the sub queries back on in an efficient manner ?
Post the query after all subqueries removed and then post subqueries separately - try to put each of them into the temp table and join with these temp tables instead of original subquery.
If it's not broken, fix it until it is.
My Blog