sql server - Generate average growth data for the missing dates -

sql server - Generate average growth data for the missing dates -

i’m working in mssql 2008 r2 , need help generating data

i have table of ranges looks this

create table [dbo].[weight]( [id] [int] identity(1,1) not null, [date] [datetime] null, [weight] [decimal](18, 2) null, )

the info have looks this

insert [weight] values ('2014-05-07', 1) insert [weight] values ('2014-05-10', 20) insert [weight] values ('2014-05-15', 80) insert [weight] values ('2014-05-25', 100)

ok want generate average growth info missing dates. missing dates want insert temp table

this how end result should like.

date weight 2014-05-07 1 2014-05-08 7.33 2014-05-09 13.66 2014-05-10 20 2014-05-11 32 2014-05-12 44 2014-05-13 56 2014-05-14 68 2014-05-15 80 2014-05-16 82 2014-05-17 84 2014-05-18 86 2014-05-19 88 2014-05-20 90 2014-05-21 92 2014-05-22 94 2014-05-23 96 2014-05-24 98 2014-05-25 100

select dateadd(day,[n],[date]) [date], [weight] + isnull([n]*([next_weight]-[weight])/[count],0) [weight] [dbo].[weight] t1 outer apply ( select top 1 datediff(day,t1.[date],[date]) [count], [weight] [next_weight] [dbo].[weight] [date] > t1.[date] order [date] ) t2 cross apply ( select top(isnull([count],1)) row_number() over(order (select 1))-1 [n] master.dbo.spt_values ) t3

sql fiddle demo

sql-server