Ah!  Sorry and understood... guess that's one of the advantages of partitioned pass through views.

Not sure what the problem with the view is... I just created two small 200k row tables with...

Heh... "Employe" is a "short timer"   Had to leave something for him to troubleshoot

Oh yeah... I like the ROW_Number thing, especially.  Haven't had the pleasure of using it in 2k5 but have done some cool things with it in (yeech) Oracle.

On the time...

Whew!  Thought I had a "senior moment" or something.  Thanks for the feed back.

I used the date comparisons I did because I didn't know if times were involved nor whether...

Uh oh... whose tests failed?  I was pretty sure I tested all 4 scenarios you posted and was pretty sure they came out right.  'Course, it's late and I might...

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.