sql - find the missing entries for the working days and fill the row with the values from the closest date -

the problem splits 2 parts. how check working days missing database, if missing add them , fill row values closest date.

first part, check , find days. should use gap approach in example below?

select t1.col1 startofgap, min(t2.col1) endofgap           (select col1 = thedate + 1  sampledates tbl1         not exists(select * sampledates tbl2                         tbl2.thedate = tbl1.thedate + 1)        , thedate <> (select max(thedate) sampledates)) t1     inner join      (select col1 = thedate - 1  sampledates tbl1         not exists(select * sampledates tbl2                         tbl1.thedate = tbl2.thedate + 1)        , thedate <> (select min(thedate) sampledates)) t2      on t1.col1 <= t2.col1     group t1.col1;  

then need see closest date 1 missing , fill new inserted date (the 1 missing) values closest. time ago, came closest value row, time need adapt check both down , upwards.

select t,a, c,y, coalesce(y,              (select top (1) y               tablet  p2                                p2.y not null                    , p2.[t] <= p.[t] , p.c = p2.c  order p2.[t] desc)) 'ynew' tablet p order c, t 

how combine one?

thanks

edit: expected result

  date          1ma  20.12.2012    0.152 21.12.2012    0.181  22 weekend it's skipped (they skipped automatically)   23 weekend -,-  24 missing   25 missing  26 missing 27.12.2012    0.173 28.12.2012    0.342     date          1ma  20.12.2012    0.152 21.12.2012    0.181  22 weekend it's skipped (they skipped automatically)   23 weekend    0.181 24 missing    0.181 25 missing    0.181 26 missing    0.173 27.12.2012    0.173 28.12.2012    0.342 

so, 24,25,26 not there null values. not there.

edit 2: taking closest value, let's consider scenario in i'm looking above. going 1 when it's missing.

date          1ma      20.12.2012    0.152     21.12.2012    0.181      22 weekend it's skipped (they skipped automatically)       23 weekend    0.181     24 missing    0.181     25 missing    0.181     26 missing    0.181      27.12.2012    0.173     28.12.2012    0.342 

for these types of query gain significant performance benefits creating calendar table containing every date you'll ever need test. (if you're familiar term "dimension tables", 1 such table enumerate every date of interest.)

also, query whole can become simpler.

select    cal.calendar_date   data_date,    case when prev_data.gap <= next_data.gap         prev_data.data_value         else coalesce(next_data.data_value, prev_data.data_value)    end        data_value     calendar   cal outer apply (     select top(1)         data_date,         data_value,         datediff(day, data_date, cal.calendar_date)   gap             data_table             data_date <= cal.calendar_date     order         data_date desc )    prev_data outer apply (     select top(1)         data_date,         data_value,         datediff(day, cal.calendar_date, data_date)   gap             data_table             data_date >  cal.calendar_date     order         data_date asc )    next_data    cal.calendar_date between '2015-01-01' , '2015-12-31' ; 

edit reply comment different requirement

to "the value above" easier, , insert values in table easy enough...

insert     data_table select    cal.calendar_date,    prev_data.data_value     calendar   cal cross apply (     select top(1)         data_date,         data_value             data_table             data_date <= cal.calendar_date     order         data_date desc )    prev_data        cal.calendar_date between '2015-01-01' , '2015-12-31'    , cal.calendar_date <> prev_data.data_date ; 

note: add where prev_data.gap > 0 bigger query above dates don't have data.