Nagaram (10/7/2012)
Here is the another solution ;
;WITH DigitsCTE AS
(
SELECT digit
FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS D(digit)
)
, AllDatesCTE AS
(
SELECT DATEADD(DAY, N.number - 1, T.min_date) AS date
FROM (SELECT MIN(T.DATECOL) AS min_date, MAX(T.DATECOL) AS max_date
FROM #SAMPLETABLE AS T) AS T
CROSS APPLY
(SELECT TOP(DATEDIFF(DAY, T.min_date, T.max_date) + 1)
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
FROM DigitsCTE AS D0, DigitsCTE AS D1, DigitsCTE AS D2,
DigitsCTE AS D3, DigitsCTE AS D4, DigitsCTE AS D5) AS N
)
SELECT CONVERT(varchar(20), N.date , 101) AS DATECOL, T.WEIGHTS
FROM AllDatesCTE AS N
CROSS APPLY
(SELECT TOP(1) DATECOL, WEIGHTS
FROM #SAMPLETABLE AS T
WHERE T.DATECOL <= N.date
ORDER BY DATECOL DESC) AS T
ORDER BY 1 ASC
By jove, you've got it. All I had to do on the larger example to make it work was to remove the convert on N.date so that it would sort correctly.
Also be advised that ORDER BY ORDINAL has been deprecated.
--Jeff Moden
Change is inevitable... Change for the better is not.