quote:

---

easiest way to do this is to use t-sql script with a cross join logic to number table to generate the intermediate rows.

---

begin tran

	set nocount on

	-- setup
	declare @MeterData table ( ReadingDate datetime, MeterName varchar(20), ConsumptionID int, EstimatedData varchar(2), LastGoodReading INT  )
	insert into @MeterData ( ReadingDate, MeterName, ConsumptionID, EstimatedData, LastGoodReading )
		select '7/10/2013', 'MeterA', 3, 'N',  132 
	union select '7/11/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/12/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/13/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/14/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/15/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/16/2013', 'MeterA', 3, 'N',  150
	union select '7/17/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/18/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/19/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/20/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/21/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/22/2013', 'MeterA', 3, 'N',  210
	union select '7/23/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/24/2013', 'MeterA', 3, 'Y',  NULL 
	union select '7/25/2013', 'MeterA', 3, 'N',  260

	-- declarations
	declare @StartDate datetime, @ThisDate datetime, @EndDate datetime, @NextGoodDate datetime, @NextBadDate datetime
	declare @EstimatedCount int, @GoodReading int, @NextGoodReading int, @DaysDifference int, @ReadingDifference int, @NextDay int
	select @StartDate = MIN(ReadingDate) from @MeterData
	select @EndDate = MAX(ReadingDate) from @MeterData
	set @EstimatedCount = 0
	set @NextDay = 1

	-- loop through each row
	while @StartDate <= @EndDate
	begin
		set @ThisDate = @StartDate
		-- first loop, get first Non Estimated Reading
		if ( select EstimatedData from @MeterData where ReadingDate = @ThisDate ) = 'N'
		begin
			select @GoodReading = LastGoodReading from @MeterData where ReadingDate = @ThisDate
			-- get the next good date
			select @NextGoodDate = min(ReadingDate) from @MeterData where ReadingDate > @ThisDate and EstimatedData = 'N'
			select @NextGoodReading = LastGoodReading from @MeterData where ReadingDate = @NextGoodDate
			select @DaysDifference = datediff(day, @ThisDate, @NextGoodDate)
			
			-- calculate
			set @ReadingDifference = (@NextGoodReading - @GoodReading) / @DaysDifference
			set @NextBadDate = dateadd(day, 1, @ThisDate)
			
			while @NextBadDate < @NextGoodDate
			begin
				-- update NULL values
				update @MeterData
				set LastGoodReading = @GoodReading + (@ReadingDifference * @NextDay)
				where ReadingDate = @NextBadDate
					and EstimatedData = 'Y'

				-- iterate
				set @NextDay = @NextDay + 1
				set @NextBadDate = @NextBadDate + 1
			end
			-- reset variables
			set @ReadingDifference = 0;
			set @NextGoodReading = 0;
			set @NextDay = 1;
		end
		-- move to the next day
		set @StartDate = dateadd(day, 1, @StartDate)
	end

	select * from @MeterData

rollback

ReadingDate			MeterName	ConsumptionID	EstimatedData	LastGoodReading
2013-07-10 00:00:00.000	MeterA		3			N			132
2013-07-11 00:00:00.000	MeterA		3			Y			135
2013-07-12 00:00:00.000	MeterA		3			Y			138
2013-07-13 00:00:00.000	MeterA		3			Y			141
2013-07-14 00:00:00.000	MeterA		3			Y			144
2013-07-15 00:00:00.000	MeterA		3			Y			147
2013-07-16 00:00:00.000	MeterA		3			N			150
2013-07-17 00:00:00.000	MeterA		3			Y			160
2013-07-18 00:00:00.000	MeterA		3			Y			170
2013-07-19 00:00:00.000	MeterA		3			Y			180
2013-07-20 00:00:00.000	MeterA		3			Y			190
2013-07-21 00:00:00.000	MeterA		3			Y			200
2013-07-22 00:00:00.000	MeterA		3			N			210
2013-07-23 00:00:00.000	MeterA		3			Y			226
2013-07-24 00:00:00.000	MeterA		3			Y			242
2013-07-25 00:00:00.000	MeterA		3			N			260