ChrisM@Work (10/8/2012)

---

dwain.c (10/8/2012)

---

ChrisM@Work (10/8/2012)

---

Hi Dwain, I came up with this last night but ran out of time to post;

;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 * --left like this for testing

FROM WholeRange w

CROSS APPLY (

SELECT TOP 1 s.*

FROM #SAMPLETABLE s

WHERE s.datecol <= w.datecol

ORDER BY s.datecol DESC

) x

You'll notice it's exactly the same as Nagaram's query - except for the number generator.

I can't find anything wrong with it?

My mistake! I didn't take into account that row ordering of the OP's query was different. Correct number of rows is returned by his, yours and my Quirky Update.

As usual, yours seems to be the fastest. :angry:

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".