dwain.c (10/8/2012)
<<snip>>Nagaram's is the fastest so far - it's a good 20% faster than mine. I'm having a look to find out why - probably the tally-table generation. Yours does an update (and very quickly too) so it's a bit "oranges and apples".
After I removed the ORDER BY yours and Nagaram's tied. Interesting that you think his should be faster.
If I'd have been able to get my Quirky Merge to work, I'd have won. 😀
It might be this lappy 😉
Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;
PRINT 'Nagaram (reformatted) =================================================='
SET STATISTICS IO, TIME ON
;WITH DigitsCTE AS (SELECT digit FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS D(digit))
, AllDatesCTE AS (
SELECT date = DATEADD(DAY, N.number - 1, T.min_date)
FROM (SELECT min_date = MIN(T.DATECOL), max_date = MAX(T.DATECOL) FROM #SAMPLETABLE AS T) AS T
CROSS APPLY (
SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
number = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
) SELECT * FROM AllDatesCTE
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM original ========================================================'
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT datecol = DATEADD(day,n,d.Startdate)
FROM (SELECT Startdate = MIN(datecol), daysSpan = 1+DATEDIFF(day,MIN(datecol), MAX(datecol)) FROM #SAMPLETABLE) d
CROSS APPLY (SELECT TOP(daysSpan) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.columns a, sys.columns b) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
PRINT ''
PRINT 'ChrisM new ============================================================='
SET STATISTICS IO, TIME ON
;WITH WholeRange AS (
SELECT x.datecol
FROM (SELECT Startdate = MIN(datecol), Enddate = MAX(datecol) FROM #SAMPLETABLE) d
CROSS APPLY (
SELECT TOP(DATEDIFF(day,Startdate, Enddate) + 1)
datecol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,d.Startdate)
FROM
(VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS g(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS h(digit),
(VALUES($),($),($),($),($),($),($),($),($),($)) AS i(digit)
) x
) SELECT * FROM WholeRange
SET STATISTICS IO, TIME OFF
Edit: fixed quote
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden