InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 02:34:33 GMT20http://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxCREATE TABLE #SAMPLETABLE(DATECOL DATETIME,WEIGHTS float) INSERT INTO #SAMPLETABLESELECT '08/09/2012',8.2 UNION ALLSELECT '08/10/2012',9.4 UNION ALLSELECT '08/14/2012',10 UNION ALLSELECT '08/15/2012',9.6 UNION ALLSELECT '08/16/2012',9.3 UNION ALLSELECT '08/19/2012',9.7 declare @MIN datetime,@max datetime,@WEIGHTS varchar(100)select @MIN=min(DATECOL),@max=max(DATECOL) from #SAMPLETABLE while @MIN<>@max begin select @WEIGHTS=WEIGHTS from #SAMPLETABLE where DATECOL=@MIN if not exists (select 1 from #SAMPLETABLE where DATECOL=@MIN)insert into #SAMPLETABLE select @MIN,@WEIGHTS set @MIN=@MIN+1 end select * from #SAMPLETABLE order by DATECOL drop table #SAMPLETABLE Mon, 08 Oct 2012 20:05:42 GMTcooljagadeeshhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxtry this alsoCREATE TABLE #SAMPLETABLE(DATECOL DATETIME,WEIGHTS float) INSERT INTO #SAMPLETABLESELECT '08/09/2012',8.2 UNION ALLSELECT '08/10/2012',9.4 UNION ALLSELECT '08/14/2012',10 UNION ALLSELECT '08/15/2012',9.6 UNION ALLSELECT '08/16/2012',9.3 UNION ALLSELECT '08/19/2012',9.7 declare @MIN datetime,@max datetime,@WEIGHTS varchar(100)select @MIN=min(DATECOL),@max=max(DATECOL) from #SAMPLETABLE while @MIN<>@max begin select @WEIGHTS=WEIGHTS from #SAMPLETABLE where DATECOL=@MIN if not exists (select 1 from #SAMPLETABLE where DATECOL=@MIN)insert into #SAMPLETABLE select @MIN,@WEIGHTS set @MIN=@MIN+1 end select * from #SAMPLETABLE order by DATECOL drop table #SAMPLETABLE Mon, 08 Oct 2012 20:04:08 GMTcooljagadeeshhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]Jeff Moden (10/8/2012)[/b][hr][quote][b]dwain.c (10/7/2012)[/b][hrJeff - I don't think this solution works with your test harness.[/quote]I did say that I needed to make a change to get it to work and described the change.[/quote][quote][b]dwain.c (10/8/2012)[/b][hr][quote]...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?[/quote]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...[/quote]Jeff - Dwain was a bit cranky when he wrote that, his comb-over took off in the wind on the way to work :-D[/quote]BWAAHAHAHA! You're assuming I have enough to comb over!Mon, 08 Oct 2012 17:22:34 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]Jeff Moden (10/8/2012)[/b][hr][quote][b]dwain.c (10/7/2012)[/b][hrJeff - I don't think this solution works with your test harness.[/quote]I did say that I needed to make a change to get it to work and described the change.[/quote]Actually I retracted tat statement later on.Mon, 08 Oct 2012 17:21:57 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]Jeff Moden (10/8/2012)[/b][hr][quote][b]dwain.c (10/7/2012)[/b][hrJeff - I don't think this solution works with your test harness.[/quote]I did say that I needed to make a change to get it to work and described the change.[/quote][quote][b]dwain.c (10/8/2012)[/b][hr][quote]...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?[/quote]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...[/quote]Jeff - Dwain was a bit cranky when he wrote that, his comb-over took off in the wind on the way to work :-DMon, 08 Oct 2012 11:31:35 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/7/2012)[/b][hrJeff - I don't think this solution works with your test harness.[/quote]I did say that I needed to make a change to get it to work and described the change.Mon, 08 Oct 2012 11:19:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/8/2012)[/b][hr]Chris - I'm gonna keep moanin' until I win one of these performance races against you.So I'll submit this (stealing your Tally table):[code="sql"] ;WITH Tally (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM (VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit), (VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit) ) , MyData AS ( SELECT DateCol, Weights ,rn=ROW_NUMBER() OVER (ORDER BY DateCol) FROM #SAMPLETABLE )SELECT DateCol=CASE WHEN c.DateCol IS NULL THEN a.DateCol ELSE c.DateCol END , a.WeightsFROM MyData aLEFT OUTER JOIN MyData b ON a.rn = b.rn - 1OUTER APPLY ( SELECT DATEADD(day, n, a.DateCol) FROM Tally WHERE n BETWEEN 0 AND DATEDIFF(day, a.DateCol, b.DateCol)-1) c(DateCol)[/code]Looks tight on my lappie![/quote]It's nice, Dwain, but using a filter on the tally table isn't as quick as using TOP;[code="sql"];WITH SequencedData AS (SELECT DateCol, Weights, rn = ROW_NUMBER() OVER (ORDER BY DateCol) FROM #SAMPLETABLE)SELECT DateCol = ISNULL(t.datecol,a.DateCol), a.Weights FROM SequencedData aLEFT OUTER JOIN SequencedData b ON a.rn = b.rn-1OUTER APPLY ( SELECT TOP(ISNULL(DATEDIFF(day, a.DateCol,b.DateCol),0)) DateCol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,a.DateCol) FROM (VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit), (VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit)) t[/code]Mon, 08 Oct 2012 07:33:24 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxChris - I'm gonna keep moanin' until I win one of these performance races against you.So I'll submit this (stealing your Tally table):[code="sql"] ;WITH Tally (n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM (VALUES($),($),($),($),($),($),($),($),($),($)) AS e(digit), (VALUES($),($),($),($),($),($),($),($),($),($)) AS f(digit) ) , MyData AS ( SELECT DateCol, Weights ,rn=ROW_NUMBER() OVER (ORDER BY DateCol) FROM #SAMPLETABLE )SELECT DateCol=CASE WHEN c.DateCol IS NULL THEN a.DateCol ELSE c.DateCol END , a.WeightsFROM MyData aLEFT OUTER JOIN MyData b ON a.rn = b.rn - 1OUTER APPLY ( SELECT DATEADD(day, n, a.DateCol) FROM Tally WHERE n BETWEEN 0 AND DATEDIFF(day, a.DateCol, b.DateCol)-1) c(DateCol)[/code]Looks tight on my lappie!Mon, 08 Oct 2012 06:07:17 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxI'm very thank ful to all .Its great pleasure to know all the valueable suggestions and solutions.Mon, 08 Oct 2012 05:25:37 GMTNagaramhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/8/2012)[/b][hr][quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]dwain.c (10/8/2012)[/b][hr]<<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".[/quote][quote]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[/quote]It might be this lappy ;-)Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;[code="sql"]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[/code]Edit: fixed quote[/quote]Did you forget something? Like possibly outputting the propagated weights?I thought output like this was what we were looking for?[code="sql"]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[/code]Not that I think my subquery version will be faster or anything. Just sayin'.[/quote]Tried it already - it's the same performance as the rest. Here's the bit you moaned about four posts back:[code="sql"]SELECT x.datecol, y.WEIGHTS FROM (SELECT Startdate = MIN(datecol), Enddate = MAX(datecol) FROM #SAMPLETABLE) d CROSS APPLY ( SELECT TOP(DATEDIFF(day,d.Startdate, d.Enddate) + 1) datecol = DATEADD(day,ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1,d.Startdate) FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS e(digit), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS f(digit), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS g(digit), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS h(digit), (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS i(digit) ) xCROSS APPLY ( SELECT TOP 1 si.WEIGHTS FROM #SAMPLETABLE si WHERE si.datecol <= x.datecol ORDER BY si.datecol DESC) yORDER BY x.datecol[/code]Mon, 08 Oct 2012 05:05:37 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/8/2012)[/b][hr][quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]dwain.c (10/8/2012)[/b][hr]I like your:[code="sql"]VALUES ($)[/code]Wonder where you got that from... :-P[/quote]I know you like them - so I put in [i]lots[/i].[/quote]I like Euros better, just don't have that character on my keyboard.[/quote]We call 'em Yoyos - next year you will probably find out why ;-)Mon, 08 Oct 2012 04:58:38 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]dwain.c (10/8/2012)[/b][hr]I like your:[code="sql"]VALUES ($)[/code]Wonder where you got that from... :-P[/quote]I know you like them - so I put in [i]lots[/i].[/quote]I like Euros better, just don't have that character on my keyboard.Mon, 08 Oct 2012 04:26:08 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/8/2012)[/b][hr]I like your:[code="sql"]VALUES ($)[/code]Wonder where you got that from... :-P[/quote]I know you like them - so I put in [i]lots[/i].Mon, 08 Oct 2012 04:21:21 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]dwain.c (10/8/2012)[/b][hr]<<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".[/quote][quote]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[/quote]It might be this lappy ;-)Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;[code="sql"]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[/code]Edit: fixed quote[/quote]Did you forget something? Like possibly outputting the propagated weights?I thought output like this was what we were looking for?[code="sql"]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[/code]Not that I think my subquery version will be faster or anything. Just sayin'.Mon, 08 Oct 2012 04:16:47 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxI like your:[code="sql"]VALUES ($)[/code]Wonder where you got that from... :-PMon, 08 Oct 2012 03:59:28 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/7/2012)[/b][hr]I find it extremely annoying that this does not work:[code="sql"]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[/code]When BOL ([url]http://technet.microsoft.com/en-us/library/bb510625.aspx[/url]) says that you should be able to SET assign to a local variable.[/quote]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.Mon, 08 Oct 2012 02:35:09 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/8/2012)[/b][hr]<<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".[/quote][quote]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[/quote]It might be this lappy ;-)Had some fun playing with the virtual tally tables. Nagaram's was definitely faster, but not by much;[code="sql"]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[/code]Edit: fixed quoteMon, 08 Oct 2012 02:31:33 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]ChrisM@Work (10/8/2012)[/b][hr][quote][b]dwain.c (10/8/2012)[/b][hr][quote][b]ChrisM@Work (10/8/2012)[/b][hr]Hi Dwain, I came up with this last night but ran out of time to post;[code="sql"];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[/code]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?[/quote]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. :angry:[/quote]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".[/quote]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. :-DMon, 08 Oct 2012 01:50:03 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/8/2012)[/b][hr][quote][b]ChrisM@Work (10/8/2012)[/b][hr]Hi Dwain, I came up with this last night but ran out of time to post;[code="sql"];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[/code]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?[/quote]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. :angry:[/quote]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".Mon, 08 Oct 2012 01:29:06 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]ChrisM@Work (10/8/2012)[/b][hr]Hi Dwain, I came up with this last night but ran out of time to post;[code="sql"];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[/code]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?[/quote]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. :angry:Mon, 08 Oct 2012 01:10:17 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxHi Dwain, I came up with this last night but ran out of time to post;[code="sql"];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[/code]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?Mon, 08 Oct 2012 00:41:02 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]Jeff Moden (10/7/2012)[/b][hr][quote][b]Nagaram (10/7/2012)[/b][hr]Here is the another solution ;[code];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[/code][/quote]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.[/quote]Jeff - I don't think this solution works with your test harness.But I think this one does:[code="sql"]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[/code]Sun, 07 Oct 2012 22:05:37 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxI find it extremely annoying that this does not work:[code="sql"]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[/code]When BOL ([url]http://technet.microsoft.com/en-us/library/bb510625.aspx[/url]) says that you should be able to SET assign to a local variable.Sun, 07 Oct 2012 21:34:26 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxthanks jeff for your valuable advice .I should change the order by n.date final select as below :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 [b] N.date[/b] ASCSun, 07 Oct 2012 21:00:46 GMTNagaramhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]Nagaram (10/7/2012)[/b][hr]Here is the another solution ;[code];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[/code][/quote]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.Sun, 07 Oct 2012 20:54:21 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxHere is the another solution ;[code];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[/code]Sun, 07 Oct 2012 20:41:32 GMTNagaramhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxI haven't come up with anything yet. I've been watchig you guys with great interest because even the previous row stuff in 2012 won't solve this problem. I've also confirmed that even larger number of rows still maintain the full cross join. If you add a unique clustered index to DATECOL, it cuts it down to triangular joins but 1000 dates still creates more tha 500,000 internal rows spinning off of the source table.I believe even a "counting" rCTE would beat the cross joins that have occurred so far.Here's the data generator that I've been using for this problem if you're interested. [code="sql"]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)[/code]Sun, 07 Oct 2012 20:34:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]Jeff Moden (10/7/2012)[/b][hr][quote][b]dwain.c (10/7/2012)[/b][hr]I don't think this has the same issue that Jeff is reporting about Mark's but it's just another option:[/quote]Gosh. Good bit of code, Dwain, but it also has a full cross join in it according to the actual execution plan. Again, I don't know if these things will "convert" to something else if the row count gets bigger or not. Guess I'll have to give it a try.I'm really interested in this problem because I normally use the Quirky Update for "data smears" like this and I'd love to see a different method that didn't end up with a cross join in it.[/quote]Hmmm. I looked for that but it must have escaped my notice.I did have a recursive CTE solution (gone now) but I expected it would be a dog so didn't post it.I thought about a QU but didn't quite figure how it could be used to create additional records. Perhaps a Quirky Merge?In any event, I am most curious what you come up with...Sun, 07 Oct 2012 20:18:28 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspx[quote][b]dwain.c (10/7/2012)[/b][hr]I don't think this has the same issue that Jeff is reporting about Mark's but it's just another option:[/quote]Gosh. Good bit of code, Dwain, but it also has a full cross join in it according to the actual execution plan. Again, I don't know if these things will "convert" to something else if the row count gets bigger or not. Guess I'll have to give it a try.I'm really interested in this problem because I normally use the Quirky Update for "data smears" like this and I'd love to see a different method that didn't end up with a cross join in it.Sun, 07 Oct 2012 20:07:05 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxI don't think this has the same issue that Jeff is reporting about Mark's but it's just another option:[code="sql"];WITH Tally AS ( SELECT n=number FROM [master].dbo.spt_values Tally WHERE [Type] = 'P' AND Number BETWEEN 1 AND 100), MyData AS ( SELECT DATECOL, WEIGHTS ,rn=ROW_NUMBER() OVER (ORDER BY DATECOL) FROM #SAMPLETABLE )SELECT DATECOL=CASE WHEN c.DATECOL IS NULL THEN a.DATECOL ELSE c.DATECOL END , a.WEIGHTSFROM MyData aOUTER APPLY ( SELECT TOP 1 DATECOL, WEIGHTS FROM MyData b WHERE b.rn = 1 + a.rn) bOUTER APPLY ( SELECT DATEADD(day, n-1, a.DATECOL) FROM Tally WHERE DATEADD(day, n, a.DATECOL) BETWEEN a.DATECOL AND b.DATECOL) c(DATECOL)[/code]Note: Should work for any gaps of 100 days or less.Sun, 07 Oct 2012 19:29:15 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxCareful, Mark. I don't know if the optimizer would make better use of a different plan for a larger number of rows but, right now, the execution plan has not one but two accidental cross-joins in it that produce 66 rows each (6 from the original data * 11 for all the desired dates).Sun, 07 Oct 2012 08:32:01 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxTry this[code="sql"]WITH Range(MINDATE,TotalDays) AS (SELECT MIN(DATECOL), DATEDIFF(Day,MIN(DATECOL),MAX(DATECOL))FROM #SAMPLETABLE),CTE AS (SELECT n.number+1 AS number, ROW_NUMBER() OVER(PARTITION BY CASE WHEN s.DATECOL IS NULL THEN 1 END ORDER BY n.number) AS rn, DATEADD(Day,n.number,r.MINDATE) AS DATECOL, s.WEIGHTSFROM master.dbo.spt_values nINNER JOIN Range r ON n.number BETWEEN 0 AND r.TotalDaysLEFT OUTER JOIN #SAMPLETABLE s ON s.DATECOL = DATEADD(Day,n.number,r.MINDATE)WHERE n.type='p')SELECT a.DATECOL, COALESCE(b.WEIGHTS,a.WEIGHTS) AS WEIGHTSFROM CTE aLEFT OUTER JOIN CTE b ON a.WEIGHTS IS NULL AND b.WEIGHTS IS NOT NULL AND b.rn = a.number - a.rnORDER BY a.number; [/code]Sun, 07 Oct 2012 03:05:36 GMTMark-101232http://www.sqlservercentral.com/Forums/Topic1369495-391-1.aspxHi experts,I have a scenario to fill in the GAPS between the dates with previousdate+1 day.here is the table DDL ,sample data and expected output CREATE TABLE #SAMPLETABLE(DATECOL DATETIME,WEIGHTS float)INSERT INTO #SAMPLETABLESELECT '08/09/2012',8.2 UNION ALLSELECT '08/10/2012',9.4 UNION ALLSELECT '08/14/2012',10 UNION ALLSELECT '08/15/2012',9.6 UNION ALLSELECT '08/16/2012',9.3 UNION ALLSELECT '08/19/2012',9.7 SELECT * FROM #SAMPLETABLEORDER BY DATECOLDATECOL WEIGHTS2012-08-09 00:00:00.000 8.22012-08-10 00:00:00.000 9.42012-08-14 00:00:00.000 102012-08-15 00:00:00.000 9.62012-08-16 00:00:00.000 9.32012-08-19 00:00:00.000 9.7What i need is to fill in the GAPS between the dates with previousdate+1 day and weights is same value as previous record values. -- Expected OutPut2012-08-09 00:00:00.000 8.22012-08-10 00:00:00.000 [b]9.4[/b][b]2012-08-11 00:00:00.000 9.42012-08-12 00:00:00.000 9.42012-08-13 00:00:00.000 9.4[/b]2012-08-14 00:00:00.000 102012-08-15 00:00:00.000 9.62012-08-16 00:00:00.000 [b]9.3[/b][b]2012-08-17 00:00:00.000 9.32012-08-18 00:00:00.000 9.3[/b]2012-08-19 00:00:00.000 9.7Please help me.Thanks,Sat, 06 Oct 2012 22:22:12 GMTNagaram