ChrisM@Work (10/8/2012)

---

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