Jeff Moden (10/7/2012)
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 - I don't think this solution works with your test harness.
But I think this one does:
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
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St