Try this

WITH Range(MINDATE,TotalDays) AS (

SELECT MIN(DATECOL),

DATEDIFF(Day,MIN(DATECOL),MAX(DATECOL))

FROM #SAMPLETABLE),

CTE AS (

SELECT n.number+1 AS number,

ROW_NUMBER() OVER(PARTITION BY CASE WHEN s.DATECOL IS NULL THEN 1 END ORDER BY n.number) AS rn,

DATEADD(Day,n.number,r.MINDATE) AS DATECOL,

s.WEIGHTS

FROM master.dbo.spt_values n

INNER JOIN Range r ON n.number BETWEEN 0 AND r.TotalDays

LEFT OUTER JOIN #SAMPLETABLE s ON s.DATECOL = DATEADD(Day,n.number,r.MINDATE)

WHERE n.type='p')

SELECT a.DATECOL,

COALESCE(b.WEIGHTS,a.WEIGHTS) AS WEIGHTS

FROM CTE a

LEFT OUTER JOIN CTE b ON a.WEIGHTS IS NULL

AND b.WEIGHTS IS NOT NULL

AND b.rn = a.number - a.rn

ORDER BY a.number;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537