>>Try materialize cte into a temp table. It often helps.
>>
>
>Thanks, I'll try that, although I thought some time ago you had suggested using a CTE to speed up another query.
>
>>Also, make sure to provide aliases for ALL columns in your queries when you use several tables. Your last select statement has columns without aliases, so it's hard to see which table they belong to.
>>
>
>You've mentioned this to me before but the standard in this database is the first three letters of each field indicates what table it's from so that is unnecessary.
>

In some situations materializing CTE into a temp table makes a lot of difference. It may not be necessary in your case, but it's worth to try.