## Need to fill the Gaps with previous value

 Author Message dwain.c SSC-Forever Group: General Forum Members Points: 43907 Visits: 6431 I find it extremely annoying that this does not work:`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`When BOL (http://technet.microsoft.com/en-us/library/bb510625.aspx) says that you should be able to SET assign to a local variable. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSC-Forever Group: General Forum Members Points: 43907 Visits: 6431 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 TORDER 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 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` My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables ChrisM@Work SSC Guru Group: General Forum Members Points: 96317 Visits: 20664 Hi Dwain, I came up with this last night but ran out of time to post;`;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`You'll notice it's exactly the same as Nagaram's query - except for the number generator. I can't find anything wrong with it? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps dwain.c SSC-Forever Group: General Forum Members Points: 43907 Visits: 6431 ChrisM@Work (10/8/2012)Hi Dwain, I came up with this last night but ran out of time to post;`;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`You'll notice it's exactly the same as Nagaram's query - except for the number generator. I can't find anything wrong with it?My mistake! I didn't take into account that row ordering of the OP's query was different. Correct number of rows is returned by his, yours and my Quirky Update.As usual, yours seems to be the fastest. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables ChrisM@Work SSC Guru Group: General Forum Members Points: 96317 Visits: 20664 dwain.c (10/8/2012)ChrisM@Work (10/8/2012)Hi Dwain, I came up with this last night but ran out of time to post;`;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`You'll notice it's exactly the same as Nagaram's query - except for the number generator. I can't find anything wrong with it?My mistake! I didn't take into account that row ordering of the OP's query was different. Correct number of rows is returned by his, yours and my Quirky Update.As usual, yours seems to be the fastest. 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". “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps dwain.c SSC-Forever Group: General Forum Members Points: 43907 Visits: 6431 ChrisM@Work (10/8/2012)dwain.c (10/8/2012)ChrisM@Work (10/8/2012)Hi Dwain, I came up with this last night but ran out of time to post;`;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`You'll notice it's exactly the same as Nagaram's query - except for the number generator. I can't find anything wrong with it?My mistake! I didn't take into account that row ordering of the OP's query was different. Correct number of rows is returned by his, yours and my Quirky Update.As usual, yours seems to be the fastest. 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. :-D My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables ChrisM@Work SSC Guru Group: General Forum Members Points: 96317 Visits: 20664 dwain.c (10/8/2012)<>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. :-DIt might be this lappy ;-)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 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`Edit: fixed quote “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps ChrisM@Work SSC Guru Group: General Forum Members Points: 96317 Visits: 20664 dwain.c (10/7/2012)I find it extremely annoying that this does not work:`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`When BOL (http://technet.microsoft.com/en-us/library/bb510625.aspx) says that you should be able to SET assign to a local variable.Dwain - I tried this too, a few months ago. It's bl@@dy irritating that it doesn't appear to work when BOL suggests it should. “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps dwain.c SSC-Forever Group: General Forum Members Points: 43907 Visits: 6431 I like your:`VALUES (\$)`Wonder where you got that from... :-P My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSC-Forever Group: General Forum Members Points: 43907 Visits: 6431 ChrisM@Work (10/8/2012)dwain.c (10/8/2012)<>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. :-DIt might be this lappy ;-)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 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`Edit: fixed quoteDid you forget something? Like possibly outputting the propagated weights?I thought output like this was what we were looking for?`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`Not that I think my subquery version will be faster or anything. Just sayin'. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!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?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables