Mike Osborne (1/11/2014)
I am using SQL server 2005, 2008 and 2012 so CTE's are OKThanks for your solution, but it looks a little messy as a view definition.
I was thinking of something using ROWNUMBER.
I think your solution has problems if there are more than one row with NULL between valid data.
what is rn? a value from your Tally table?
as in dateadd(day,rn-1,o1.dateval)
Well yes - the solution does get a bit cleaner using rownumber and CTE's: for one thing you don't need the temp table declared (which would be the difference between a view and not).
As Jeff would describe it, if you're looking to "smear" data of a series of days but want to smear it over nulls or blanks, just exclude them from your initial orderedgaps table. This solution will NOT work if you want to smear ONE column but keep the other (that gets to be a lot more involved).
and yes - my tally table uses RN as the column name for the incrementing numbers. just missed one of the references (jeff's version uses N as the name).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?