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 N.date 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

quote:

---

Originally posted by Ranjit.ileni

Hi malpashaa,

In AllDatesCTE to select all the dates instead of using DigitsCTE with 6 cross joins
we can go for recursive CTE to loop through the min and max date to select all the dates.

Thanks,
Ranjit

--Irk

---