Here is another approach with no ridiculus tally table pinned into memory, where more vital information can be stored and used.

With the original sample data, there is now only 67...

When...

Also asked here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96535

I used

CREATE CLUSTERED INDEX IX_Yak ON TestData (StaffName, ReviewDate)

Please reread my previous post with timings and execution plans involved.

My suggestion only needs 32 reads whereas Sergiy needs 4385 reads...

What? I have only two table scans. And if you index the #TestData table properly you will get two clustered index scans only!

Peso with 32 reads

|--Compute Scalar

...

And this how you do it in SQL Server 2005 with only valid member (member has a reviewdate in wanted range)SELECTCASE WHEN u.RecID = 1 THEN u.StaffName ELSE '' END...

This is how you do it in SQL Server 2005 with all members includedSELECTCASE WHEN u.RecID = 1 THEN u.StaffName ELSE '' END AS StaffName,

MAX(CASE WHEN u.ReviewDate LIKE '2006%' THEN...

Why use UPDATE at all? Or use a function? This is possible to do in a single query.

And still have some options to include or exclude "non-valid" members!

SELECTCASE WHEN u.theIndex...

Replace DISTINCT with TOP 1 in the correlated subquery.

DBCC CHECKIDENT can be used to reseed the identity value.

Use OPENROWSET function together with INNER JOIN.

Or create a unique constraint (or check the ignore duplicate key) on the existing index.

then you always can insert directly and if a record exists, you will get a warning.

LEN() function is NOT counting trailing spaces.

DATALENGTH() does.

Are you the spaces really are spaces (ascii value 32)?

Make sure they are not hard-spaces (ascii value 160).

I don't think points are valid date separator for style 103.

I don't think points are valid date separator for style 103.