sql - Achieve 3 days moving average -

i having trouble task in sql server. name of table 'test'.

create table test  (     [entry_date] [date] not null,     [value] [int] not null,     [id] [int] null, )  insert test values ( '2012-02-01', 10, 1); insert test values ( '2012-02-02', 20, 2); insert test values ( '2012-02-03', 10, 1); insert test values ( '2012-02-04', 30, 2); insert test values ( '2012-02-05', 10, 1); insert test values ( '2012-02-06', 11, 3); insert test values ( '2012-02-06', 40, 1); insert test values ( '2012-02-07', 10, 2); insert test values ( '2012-02-08', 50, 3); insert test values ( '2012-02-09', 10, 2); insert test values ( '2012-02-10', 60, 2); insert test values ( '2012-02-10', 50, 4); insert test values ( '2012-02-11', 51, 3); 

based on above data, want output below:

   entry_date |value |id |averagevaluesof_3days -----------+------+---+---------------------- 2012-02-01 |10    |1  | 0       -----here donot have last3rdaysdate 2012-02-03 |10    |1  | 6.6-------last 3days feb3 , feb2 , feb1 2012-02-05 |10    |1  | 6.6 2012-02-06 |40    |1  | 0 ----here donot have last3rdaysdate(4th date missed) 2012-02-02 |20    |2  | 0 2012-02-04 |30    |2  | 16.6 2012-02-07 |10    |2  | 0 2012-02-09 |10    |2  | 6.6 2012-02-10 |60    |2  | 0 2012-02-06 |11    |3  | 0 2012-02-08 |50    |3  | 20.3 2012-02-11 |51    |3  | 0 2012-02-10 |50    |4  | 0 

i need calculate average values of running last 3 days of information @ date (entry_date) based on id wise.

if entry_date not have last 3rd day of information @ date (entry_date) consider dates average 0.

let me provide example further explaining goal:

 here need consider based on id wise  if consider id=1 related information  - if `entry_date: 2012-02-01`, date have value , 2 days don't have value less 2012-02-01 average value 0   - if `entry_date: 2012-02-03`, date have value , 2 days means 2012-02-01 , 2012-01-02. here can take 3 days average 2012-02-03 date 6.6 ((10+0+10)/3 =6.6) if take 2012-02-05 6.6 ((10+0+10)/3 = 6.6) - if `entry_date: 2012-02-01`, date have value , 2 days don't have value less 2012-02-01 average value 0   if consider id=2 related information - if `entry_date: 2012-02-02`, date have value , 2 days means 2012-02-01 , 2012-01-31,don't have value average value 0 - if `entry_date: 2012-02-04`, date have value , 2 days means 2012-02-03 , 2012-02-02. here can take 3 days average 2012-02-04 date 16.6 ((30+0+20)/3 =16.6) if take 2012-02-07 0 because of last 3rd date dont have  - if `entry_date: 2012-02-09`, date have value , 2 days means 2012-02-08 , 2012-02-07. here can take 3 days average 2012-02-09 date 6.6 ((10+0+10)/3 =6.6) if take 2012-02-10 0 because of last 3rd date dont have 

please tell me how write query achieve task sql server.

try this,

   select x.entry_date     ,x.value     ,x.id     ,convert(decimal(18, 2), case              when (                     select count(*)                     test                     id = x.id                         , entry_date <= (x.entry_date)                     ) >= 3                 avg(value) on (                         partition id order id                             ,entry_date rows between 2 preceding                                 , current row                         )             else 0             end) rollingavg (     select id         ,entry_date         ,convert(decimal(18, 2), sum(value)) value     test     group id         ,entry_date     ) x