Need to fill the Gaps with previous value

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 t
USING Tally s
ON t.DATECOL = DATEADD(day, n, @STDate)
WHEN MATCHED THEN
UPDATE SET @Weight = WEIGHTS
WHEN NOT MATCHED THEN
INSERT (DATECOL, WEIGHTS)
VALUES (DATEADD(day, n, @STDate), @Weight);

SELECT *
FROM #SAMPLETABLE
ORDER BY DATECOL

DROP 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 T
ORDER 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 ac2

CREATE 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 T
ORDER BY 1 ASC
SELECT 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 #Temp
SELECT DATECOL=n, WEIGHTS
FROM Tally
LEFT OUTER JOIN #SAMPLETABLE ON n = DATECOL

UPDATE t
SET @Weight = WEIGHTS = CASE WHEN WEIGHTS IS NULL THEN @Weight ELSE WEIGHTS END
FROM #Temp t

SELECT * FROM #Temp
SELECT 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 testing
FROM WholeRange w
CROSS 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 AllDatesCTE
SET STATISTICS IO, TIME OFF

PRINT ''
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 WholeRange
SET STATISTICS IO, TIME OFF

PRINT ''
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 WholeRange
SET STATISTICS IO, TIME OFF

ChrisM@Work (10/8/2012)

dwain.c (10/8/2012)

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

After I removed the ORDER BY yours and Nagaram's tied. Interesting that you think his should be faster.

If I'd have been able to get my Quirky Merge to work, I'd have won. BigGrin

It might be this lappy Wink

Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;

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 AllDatesCTE
SET STATISTICS IO, TIME OFF

PRINT ''
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 WholeRange
SET STATISTICS IO, TIME OFF

PRINT ''
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 WholeRange
SET STATISTICS IO, TIME OFF

Edit: fixed quote

DECLARE @STDate DATETIME, @EDate DATETIME
SELECT @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
) END
FROM Calendar a
LEFT OUTER JOIN #SAMPLETABLE b ON n = DATECOL