I haven't come up with anything yet. I've been watchig you guys with great interest because even the previous row stuff in 2012 won't solve this problem. I've also confirmed that even larger number of rows still maintain the full cross join. If you add a unique clustered index to DATECOL, it cuts it down to triangular joins but 1000 dates still creates more tha 500,000 internal rows spinning off of the source table.

I believe even a "counting" rCTE would beat the cross joins that have occurred so far.

Here's the data generator that I've been using for this problem if you're interested.

CREATE TABLE #SAMPLETABLE

(

DATECOL DATETIME,

WEIGHTS float

)

INSERT INTO #SAMPLETABLE

SELECT TOP 1000

DateCol = DATEADD(mm,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1),'1900'),

Weights = RAND(CHECKSUM(NEWID()))*10

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CREATE UNIQUE CLUSTERED INDEX ByDate ON #SampleTable (DateCol)