I may be a bit of a stick in the mud, but to me the self-aggrandizing element of blogging turns it into a non-starter for me. I appreciate that...

Earn a living from my hobby? That would turn my hobby into my profession, which makes it no longer a hobby, by definition.

Good lord, no! I...

Ah, yes. Thanks. I suspected there was a better way...

Results of performance testing with and without a CTE:

Without (the question was whether a BETWEEN clause for the date would slow down things):

SELECT a.Geography

,a.Product

,a.AsOf

,a.UPC_1

,a.WtdDist_1

,a.Dsales_1

,a.Csales_1

,a.Usales_1

,b.Brand

,CONVERT(DATETIME,(SUBSTRING(CONVERT(CHAR(8),a.AsOf),5,2)+'/'+RIGHT(CONVERT(CHAR(8),a.AsOf),2)+'/'+LEFT(CONVERT(CHAR(8),a.AsOf),2)))

AS Date

FROM IRI a

JOIN #t b...

Thanks. This morning's task is to refine that date lookup method. I am going to test your join suggestion vs a CTE vs my original between. D....

I wondered about that (page splits) last night as I couldn't sleep. Thanks for confirming it.

In the meantime, an index on AsOf (the date INT field) improved...

Yes, I am considering partioning it, but I cannot begin thinking about that for at least a few months. The loading etc. of this table was so stressful (on...

I, too, lived in the UK during the mad cow hoopla, and as a consequence the U.S. forbids me from donating... I have heard they are going to start...

Got it! thanks guys!

No, I didn't (at least not on using the large tally table). Yesterday I dropped the large tally tables and recreated small ones. dl

And no - there is no reason to use ORDER BY every time you use the tally table. There is reason to use ORDER BY every time you expect ordered...

In principle, there is no reason for large tally tables to work. If you change your query as I suggested, SQL Server can weed out the high numbers before starting...

Hi guys: Thanks for hanging in there through all my bumbling. I'm not a sql server pro, and as a result find myself in over my head quite...

The code used to create the tally table was cut and pasted from Jeff's article (with a few of my comments in the header):

--=============================================================================

-- Uncomment the "use TempDB"...

A reasonably sized (12000 rows) tally table took care of

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

--===== Find the min and max dates in the range of data

SELECT @DateStart...