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).

WHERE IN is not normally an acceptable substitute for a good old fashioned inner join or equi join and tends to be a bit slower.  WHERE NOT IN, however, has...

While Steve's post may have been pushing the boundaries a little Jeff, I still appreciate his response.

If you're...

Not sure how I'd pose the question in such a manner to see how many DBAs could get the answer right without using QA... any ideas?