OK thanks, that surely can't be right though! We have a bit of bad design here and there too but this particular section looks OK and it isn't an unreasonable query. I'm not even sure it will help in all cases either. I have seen it happen where the end date in the range is today and if we have no transactions for today it does it by date even though the start date of the range is a month or more ago. No matter how many times I recalc the stats it won't make any difference until we actually have some transactions!

The whole thing just seems broken and I don't understand why I have to put so much work into something so simple!

Maybe I'll go with the coalesce hack for now after all :unsure:

Oh and I defragment every week too if it is more than 25% fragmented. Fragmentation doesn't make any difference to query plan choice though does it?