Harley, did you try the approach I suggested on my first posting on page 2?

This additional table will contain as many different date columns as you need in one.

This...

Use LEFT JOIN

Users? Which users?

You not suppose to know where they live!

It should be "(#&($^$%!!!!-ing stupid developers!!!"

Kenneth,

actually CLR is text processing, it's not about database at all.

No wonder SQL is not optimal for tasks it was not designed for.

I would suggest not to use T-SQL also...

Yes, Andy,

you are right about dynamic SQL.

But it does not mean dynamic SQL must be built outside SP.

SQL injections, security issues (e.g. not every DBA is happy to let...

Harley, do simple exercise:

CREATE TABLE dbo.SalesChanges (

SalesID int,

ActionID tinyint, -- Say, 1 = CREATE, 2 = Insert, 3 = Close, etc.

ActionDate

)

CREATE CLUSTERED INDEX CX_ActionDate ON dbo.SalesChanges(ActionDate)

CREATE INDEX IX_SalesID ON dbo.SalesChanges(SalesID)

CREATE...

I think Antares is probably on the right trail...

Hmm...

Is it easier to embed generic SQL into application...

Did you actually try this approach?

I mean including right indexing.

How many ID's you need to include in report to make it over 1s to run?

What makes you think that this...

The best you can do here is to set up clustered index on (Col1, Col2)

And don't load the whole table. If you will select limited number of ID's to present...

Bad for you.

Do you have at least one query selecting sequential SalesID's?

WHERE SalesID between ### and ###

?

If not then there is no use for that clustered index in your...

Then you need just make the index on this column clustered.

Try to avoid querying system tables, especially from outside of database.

You need to have quite relaxed security model to perform it, and one day one pissed off employee could make...

What's datatype for TRD.SalesDate?

Bad design always leads to ugly queries and maintenance problems.

NULLIF works faster than CASE and makes your code more clean.