Yap... we've all written "those"... see the update I just posted to my previous message...

Sure, Remi... thanks for the interest...

Personally, I think triangular joins should never be used because it's an absolutely insane form of RBAR (a "Modenism" for "Row By Agonizing Row").  Here's an example...

X/2 records is the difference

Not sure why Mark thinks one is necessary here even with his latest explanation.  Still trying to figure it...

I guess I don't have enough information... first, I'm not sure why you need this...

join Number_list n

on n.number < t.Person_count --This makes a triangular join!!!

WHERE  n.number < @Max_person_count

Second, are...

Richard,

Each instance of CASE must have an END...  You have 3 instances of CASE but only 1 END.  That would be the problem.

However, the other guys are right... in this...

Here's one way... nasty fast... takes 60-123 milliseconds for 11,000 rows (more than 30 years worth of dates if needed) and 5.8 to 9.2 seconds for a million rows (if TempDB doesn't need to...

You bet. Tuan... thank you for the feedback...

Thanks Trystan,

Looking back at it, one of the CASE statements is simply overkill... replacing this part should also work with a tiny bit more speed...

--===== Determine the rest...

You bet, Ramesh... thank you for the feedback.

I don't know what I'm missing on this for you... I'll check with my network guys on Tuesday.  Sorry I wasn't able to help you without getting help myself

I agree...

Steve... just in case you are interested...

http://www.sqlservercentral.com/community/reviews.asp

Just a bit of clarification... I've not tested WHERE NOT IN vs OUTER WHERE NULL on sp4... I've seen several differences in execution plans between sp3a and sp4...

It would usually be someone who has a role of "Domain Admin" (I think that's what they call it but not sure... I'm not a network type of guy).