declare @MaxDate date
declare @MinDate date
select @MaxDate = MAX([Date]),
@MinDate = MIN([Date])
from Dates
declare @MaxValue int
declare @MinValue int
select @MaxValue = [Value] from Dates where [Date] = @MaxDate
select @MinValue = [Value] from Dates where [Date] = @MinDate
declare @diff int
select @diff = DATEDIFF(d, @MinDate, @MaxDate)
declare @increment int
set @increment = (@MaxValue - @MinValue) / @diff
select @increment
declare @jaggedDates as table
(
PID INT IDENTITY(1,1) PRIMARY KEY,
ThisDate date,
ThisValue int
)
declare @finalDates as table
(
PID INT IDENTITY(1,1) PRIMARY KEY,
[Date] date,
Value int
)
declare @thisDate date
declare @thisValue int
declare @nextDate date
declare @nextValue int
declare @count int
insert @jaggedDates select [Date], [Value] from Dates
select @count = @@ROWCOUNT
declare @thisId int
set @thisId = 1
declare @entryDiff int
declare @missingDate date
declare @missingValue int
while @thisId <= @count
begin
select @thisDate = ThisDate,
@thisValue = ThisValue
from @jaggedDates
where PID = @thisId
insert @finalDates values (@thisDate, @thisValue)
if @thisId < @count
begin
select @nextDate = ThisDate,
@nextValue = ThisValue
from @jaggedDates
where PID = @thisId + 1
select @entryDiff = DATEDIFF(d, @thisDate, @nextDate)
if @entryDiff > 1
begin
set @missingDate = @thisDate
set @missingValue = @thisValue
while @entryDiff > 1
begin
set @missingDate = DATEADD(d, 1, @missingDate)
set @missingValue = @missingValue + @increment
insert @finalDates values (@missingDate, @missingValue)
set @entryDiff = @entryDiff - 1
end
end
end
set @thisId = @thisId + 1
end
select * from @finalDates