DECLARE @Weight FLOAT = 0 ,@STDate DATETIME ,@EDate DATETIME SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE;WITH Tally AS ( SELECT TOP (1+DATEDIFF(day, @STDate, @EDate)) n=number-1 FROM [master].dbo.spt_values Tally WHERE [Type] = 'P' AND number BETWEEN 1 AND 100)MERGE #SAMPLETABLE tUSING Tally sON t.DATECOL = DATEADD(day, n, @STDate)WHEN MATCHED THEN UPDATE SET @Weight = WEIGHTSWHEN NOT MATCHED THEN INSERT (DATECOL, WEIGHTS) VALUES (DATEADD(day, n, @STDate), @Weight);SELECT * FROM #SAMPLETABLEORDER BY DATECOLDROP TABLE #SAMPLETABLE

;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 TORDER BY 1 ASC

CREATE TABLE #SAMPLETABLE(DATECOL DATETIME,WEIGHTS float) INSERT INTO #SAMPLETABLE SELECT TOP 1000 DateCol = DATEADD(mm,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1),'1900'), Weights = RAND(CHECKSUM(NEWID()))*10 FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2CREATE UNIQUE CLUSTERED INDEX ByDate ON #SampleTable (DateCol)DECLARE @StartDT DATETIME = GETDATE()CREATE TABLE #Temp(DATECOL DATETIME,WEIGHTS float)CREATE UNIQUE CLUSTERED INDEX ByDate ON #Temp (DateCol);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 TORDER BY 1 ASCSELECT StartDate=@StartDT, EndDate=GETDATE(), ElapsedMS=DATEDIFF(ms, @StartDT, GETDATE())SELECT @StartDT = GETDATE()DECLARE @Weight FLOAT = 0 ,@STDate DATETIME ,@EDate DATETIME SELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE;WITH Tally (n) AS ( SELECT TOP (1+DATEDIFF(day, @STDate, @EDate)) DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STDate) FROM sys.all_columns a CROSS JOIN sys.all_columns b)INSERT INTO #TempSELECT DATECOL=n, WEIGHTSFROM TallyLEFT OUTER JOIN #SAMPLETABLE ON n = DATECOLUPDATE t SET @Weight = WEIGHTS = CASE WHEN WEIGHTS IS NULL THEN @Weight ELSE WEIGHTS ENDFROM #Temp tSELECT * FROM #TempSELECT StartDate=@StartDT, EndDate=GETDATE(), ElapsedMS=DATEDIFF(ms, @StartDT, GETDATE())DROP TABLE #SAMPLETABLE, #Temp

;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 testingFROM WholeRange wCROSS APPLY ( SELECT TOP 1 s.* FROM #SAMPLETABLE s WHERE s.datecol <= w.datecol ORDER BY s.datecol DESC) x

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 AllDatesCTESET STATISTICS IO, TIME OFFPRINT ''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 WholeRangeSET STATISTICS IO, TIME OFFPRINT ''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 WholeRangeSET STATISTICS IO, TIME OFF

VALUES ($)

DECLARE @STDate DATETIME, @EDate DATETIMESELECT @STDate=MIN(DATECOL), @EDate=MAX(DATECOL) FROM #SAMPLETABLE;WITH Calendar (n) AS ( SELECT TOP (1+DATEDIFF(day, @STDate, @EDate)) DATEADD(day, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1, @STDate) FROM sys.all_columns a, sys.all_columns b)SELECT DATECOL=n ,WEIGHTS=CASE WHEN WEIGHTS IS NOT NULL THEN WEIGHTS ELSE ( SELECT TOP 1 WEIGHTS FROM #SAMPLETABLE c WHERE c.DATECOL < n ORDER BY c.DATECOL DESC ) ENDFROM Calendar aLEFT OUTER JOIN #SAMPLETABLE b ON n = DATECOL