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 ...