CODEWITHSUNDEEP
sql-server
Woelig
Woelig

Reputation: 62

Generate average growth data for the missing dates

I’m working in MSSQL 2008 R2 and need some help with generating data

I have a table of ranges that looks like this

CREATE TABLE [dbo].[Weight](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Date] [datetime] NULL,
    [Weight] [decimal](18, 2) NULL,
)

The data I have looks like this

INSERT INTO [Weight] VALUES ('2014-05-07', 1)
INSERT INTO [Weight] VALUES ('2014-05-10', 20)
INSERT INTO [Weight] VALUES ('2014-05-15', 80)
INSERT INTO [Weight] VALUES ('2014-05-25', 100)

Ok now what I want to do is generate average growth data for the missing dates. The missing dates I want to insert into a temp table

This is how the end result should look 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

Upvotes: 2

Views: 63

Answers (1)

Anon
Anon

Reputation: 10908

  SELECT
    DATEADD(day,[n],[date]) [Date],
    [weight] + ISNULL([n]*([next_weight]-[weight])/[count],0) [Weight]
  FROM [dbo].[Weight] t1
  OUTER APPLY (
    SELECT TOP 1
      DATEDIFF(day,t1.[date],[date]) [count],
      [weight] [next_weight]
    FROM [dbo].[Weight]
    WHERE [date] > t1.[date]
    ORDER BY [date]
  ) t2
  CROSS APPLY (
    SELECT TOP(ISNULL([count],1)) 
      ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1 [n]
    FROM master.dbo.spt_values
  ) t3

SQL Fiddle demo

Upvotes: 3

Related Questions