Thanks to everyone for your responses. Craig has the answer 'cos I'm used to using calendar tables. But I do like Autos cte as well - it's self-contained.

I...

Forgive me for being grumpy and I do appreciate your offer of help - really. But I thought my original post was clear. The table holds records for...

Your reply is most unhelpful. I suspect I may stop using SSC in future if this is the sort of reply I get.

To make it clear try this:

Create table...

Thank you all for your comments. I think Grant has put me on the right track though because it's not just one report query that's slow - it's everything....

Grant

First - I think it is awesome that the best dba in the whole world should respond so rapidly to my call! I am almost overwhelmed.

Second - I...

Grant - I'm not aware of the detailed process but I'm told it involves switching the receiving database into simple recovery mode. Does that clear the cache and is...

They couldn't afford you Grant - that's why I'm there!

Thanks Grant

Now . . . if you enjoy it so much perhaps you could pop round and sort out my clients problems - it would save all that studying....

Grant

Thanks, I've already read some of your articles and I got your book just a couple of days ago. I haven't had much time to read it yet...

This is the first time I have ever seen practical and really meaningful advise about the "black art" of query optimization. The timing is perfect because I need to...

My solution would be to write the sql code in a procedure and generate it dynamically. For example:

create proc MyProc

@Conditional varchar(10) = (For example) '<='

...

It's quite obvious really - isn't it. Now I just need to ensure the stored procedure will accept a NULL start and end date and return an empty record...

Thank you for your response but . . .

Actually there's no problem with the ssis at all! It's just the user that's defective!

Let this be a lesson to...

I agree with SSCrazy. The class and other custom code is wholy unnecessary. It's a simple matter to either generate a hex colour code string in the SQL...

I think I've fixed this but the solution is as bizarre as the problem.

The cause seems to be the nested IIF statement - though why I've no idea.

The solution...