p.p.s..... you don't need a CTE for this... the derived tables have nothing in common.  EXCLUDE will probably work but that's a poor man's excuse for a good solid OUTER...

By the way... it looks like it'll be a tiny bit faster if you change this...

     ON inc.EmployeeID = exc.EmployeeID

... to this...

     ON a.EmployeeID = exc.EmployeeID

Heh... sorry Jason... had to make sure... the post was too perfect (well done, by the way!!!)... you should teach!  You had "Use Cases" and everything!

Anyway, using the test tables...

Outstanding post, Jason... everything is there and very easy to read... while I'm looking at this, tell me what you've tried just so reassure me that this isn't homework, please.

Halifaxdal,

I/we are trying to help... in order to do that, I need answers to the questions I posed, earlier.  Of course, if you don't want the help or think you can...

David,

What I was looking at were the numbers from one of the previous posts (reprinted here for convenience...)

Warning: The table 'IISLog' has been created but its maximum row size (13625)...

Thanks for the kudo, Dave.

Did you even try to figure out the error, Frances?  Remember, lot's of folks don't have the time to setup test data if you don't provide it in an INSERT/SELECT...

A 25 column covering index just might do it ... but I'm thinking the view is written incorrectly.  Do you have calculations on the...

I'm thinking you still need to remove the "e." from the column alias... everything else in David's post should be just about spot on.

Oh.... that's why the turnover rate is so high

Perfect... thank you for the feedback!

Somebody's not listening, Brian.  WHAT does the EXE do?  The reason I ask is because you may not need the darned thing at all.

Halifaxdal...

I just noticed that the size of the MDF table doubled when you added the column... you've done something wrong... very wrong... stop modifying the original table!!!

Heh... point well taken, David... we have a day or two just like that.  Our "big one" is affectionately known as "Cycle 28".