Try a calendar table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

If there was a simple trick , then the optimizer would probably be doing it already.

Since you hands are so severely tied , there really is not much that you...

Welcome on board.

There is no such thing as a silly question, just bad questions.

Take a quick look at this article...

Try a calendar table

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

Since this has been bumped ,

a calendar table might be a good option here

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

What are you expecting to happen when A.empid is null , presently it will join to every row in K where Empid is null. Is that what you want...

Hi ,

i believe you may of missed the more relevant point here.

Your join condition is

On IsNull(A.empid,'') = IsNull(A.empid,'')

You are join table A back to table A , this...

It will 'end' at some point.

But consider this ,

every row in OBBEAVER.RevenueCA.dbo.Empdates will be joined to every row in IDMember.

Is this really what you want ?

I doubt it somehow.

One obvious problem is that you are not joining K to A at all , so that will be a Cartesian join

Insert Into Emp_COT Select K.ID, A.startdate, A.enddate

From OBBEAVER.RevenueCA.dbo.Empdates...

Please see this link http://www.karaszi.com/SQLServer/info_datetime.asp for all you could ever wish to know about dates in SqlServer

We've been here before havent we ?

http://www.sqlservercentral.com/Forums/FindPost986414.aspx

Sorry but im going to repeat my advice in that post....

This is tough to diagnose like this , though i feel the answer is...

Please post all DDL including table , view and index definition

Since this is a stupid nonsensical restriction 😉 , heres mine

Use denali

select * from #SAL

ORDER ...

Hi , try this link

essential reading IMO : http://www.karaszi.com/SQLServer/info_datetime.asp

Be careful when comparing costs within the plan , they are ALL estimated costs. The actual costs could differ dramatically.