Thanks to all, that's great. Yes, I'm storing these dates in a table for future use.

Last one, a variation on the same, this time to find future Fridays.

select @CalendarDate =...

Thanks.

I've just checked and my two Ref columns could actually be up to 50 wide which I think is the reason I had the clustered index only on the...

"Never, never use MONEY data types; their math is wrong!"

Bit off topic but I wasn't aware of this and have used MONEY columns occasionally. Could you elaborate and should I...

select @NewT = dateadd(day, -1 * datediff(day, '01/01/1900', @OrigDT), @OrigDT)

Seems to do it?

Just an update, decided to abandon the cursor method for calculating running totals and went straight to the Quirky Update - followed Jeff's article to the letter and it works...

Imagine that you have 20 rows in the outer query and you need an aggregation of 10-50 related records in another table (TableB) for each of the 20. If...

I think I've covered that one by rebuilding the running totals from the earliest date in the table that will be affected by the new batch of rows. Of course...

Given the implications for me, you'd better tell me sooner rather than later....

Thanks to all for the advice, I'll go away and try to re-write my running total...code will follow if I hit problems!

There seems to be two issues:

1. How to calculate...

Thanks. I presume it can be done without a calendar table, but on looking at the link provided I can see the advantages of it as I have to do...

That seems to do it. I'd experimented with case and isnull but never got as far as using them using together. Thanks for this!

One more question. I'm currently on 2005 and hope to move to 2008 R2 shortly. I haven't looked into yet but I was thinking about changing my 'upserts' to using...

Wow, thanks Paul - great article! I'd got as far as seeing the problems in writing out the combinations, coalesce and isnull, but would never have thought of using intersect...

Sorry, haven't got access to my sql code at the moment but the example I've given matches it. The only nullable column is A and it's a one-to-one relationship between...

count(@A) will work in the cut-down example I posted using only variables, a select without any table referenced. count(ColA) won't of course work without a sub-query.

I came across the...