title: SQL Fill in Gaps with last value untill new value occurs
body: <p>I have a dataset with gaps in the status column. Similar questions on here have the missing dates, but with mine the dates are given. In this example I need the latest status to fill in the NULL gaps below until a new status occurs then that new status needs to fill in the gaps.</p>
  
  <blockquote>
    <p>ex:<br>
    create table #tableA<br></p>
    
    <p>(<br>
    Campaign Varchar (100)<br>
    ,Date_of_data Date<br>
    ,Status varchar (100)<br>
    )</p>
    
    <p>insert into #tableA<br>
    values<br>
    ('banner1','11/2/2015','DELIVERING'),<br>
    ('banner1','11/3/2015','DELIVERING'),<br>
    ('banner1','11/4/2015',null),<br>
    ('banner1','11/5/2015',null),<br>
    ('banner1','11/6/2015',null),<br>
    ('banner1','11/7/2015',null),<br>
    ('banner1','11/8/2015',null),<br>
    ('banner1','11/9/2015','PAUSED'),<br>
    ('banner1','11/10/2015','PAUSED'),<br>
    ('banner1','11/11/2015','PAUSED'),<br>
    ('banner1','11/12/2015',null),<br>
    ('banner1','11/13/2015',null),<br>
    ('banner1','11/14/2015',null),<br>
    ('banner1','11/15/2015',null),<br>
    ('banner1','11/16/2015','DELIVERING'),<br>
    ('banner1','11/17/2015','DELIVERING'),<br>
    ('banner1','11/18/2015','DELIVERING')<br></p>
    
    <p>select * from #tableA<br></p>
  </blockquote>
  
  <p>the status 'DELIVERING' on 11/3 needs to fill in for 11/4-11/8 and then the status of 'PAUSED' on 11/11 needs to fill in for 11/12-11/15 so the final data set would effectively look as such</p>
  
  <blockquote>
    <p>Campaign|Date_of_data|Status<br>
    banner1    2015-11-02  DELIVERING<br>
    banner1    2015-11-03  DELIVERING<br>
    banner1    2015-11-04  DELIVERING<br>
    banner1    2015-11-05  DELIVERING<br>
    banner1    2015-11-06  DELIVERING<br>
    banner1    2015-11-07  DELIVERING<br>
    banner1    2015-11-08  DELIVERING<br>
    banner1    2015-11-09  PAUSED<br>
    banner1    2015-11-10  PAUSED<br>
    banner1    2015-11-11  PAUSED<br>
    banner1    2015-11-12  PAUSED<br>
    banner1    2015-11-13  PAUSED<br>
    banner1    2015-11-14  PAUSED<br>
    banner1    2015-11-15  PAUSED<br>
    banner1    2015-11-16  DELIVERING<br>
    banner1    2015-11-17  DELIVERING<br>
    banner1    2015-11-18  DELIVERING<br></p>
  </blockquote>
  
  <p>Thanks!</p>
  
  <p>Second scenario....</p>
  
  <p>The below solutions work, but I have run into a new scenario. The below works great for "banner1" but when you get to "banner2" the records for 11/12-11/15 don't get filled with 'READY'. They fill with 'PAUSED' and Im not sure why. Is it an ordering issue?<br></p>
  
  <p>Desired logic: fill NULL records with the first "previous" status<br></p>
  
  <blockquote>
    <p>drop table  #tableA<br>
    create   table #tableA<br>
    (<br>
      Campaign Varchar (100),<br>
       Date_of_data Date,<br>
       Status varchar (100)<br>
    )<br></p>
    
    <p>insert into #tableA<br>
    values<br>
       ('banner1','11/2/2015','DELIVERING'),<br>
       ('banner1','11/3/2015','DELIVERING'),<br>
       ('banner1','11/4/2015',null),<br>
       ('banner1','11/5/2015',null),<br>
       ('banner1','11/6/2015',null),<br>
       ('banner1','11/7/2015',null),<br>
       ('banner1','11/8/2015',null),<br>
       ('banner1','11/9/2015','PAUSED'),<br>
       ('banner1','11/10/2015','PAUSED'),<br>
       ('banner1','11/11/2015','PAUSED'),<br>
       ('banner1','11/12/2015',null),<br>
       ('banner1','11/13/2015',null),<br>
       ('banner1','11/14/2015',null),<br>
       ('banner1','11/15/2015',null),<br>
       ('banner1','11/16/2015','DELIVERING'),<br>
       ('banner1','11/17/2015','DELIVERING'),<br>
       ('banner1','11/18/2015','DELIVERING'),<br>
      ('banner2','11/2/2015','DELIVERING'),<br>
       ('banner2','11/3/2015','DELIVERING'),<br>
       ('banner2','11/4/2015',null),<br>
       ('banner2','11/5/2015',null),<br>
       ('banner2','11/6/2015',null),<br>
       ('banner2','11/7/2015',null),<br>
       ('banner2','11/8/2015',null),<br>
       ('banner2','11/9/2015','READY'),<br>
       ('banner2','11/10/2015','READY'),<br>
       ('banner2','11/11/2015','READY'),<br>
       ('banner2','11/12/2015',null),<br>
       ('banner2','11/13/2015',null),<br>
       ('banner2','11/14/2015',null),<br>
       ('banner2','11/15/2015',null),<br>
       ('banner2','11/16/2015','DELIVERING'),<br>
       ('banner2','11/17/2015','DELIVERING'),<br>
       ('banner2','11/18/2015','DELIVERING')<br></p>
    
    <p>SELECT Campaign, Date_of_data, <br>
          COALESCE(Status, prevStatus) AS Status  <br><br>
    FROM #TableA AS t1<br>
    OUTER APPLY (<br>
      SELECT TOP 1 Status<br>
      FROM #TableA AS t2<br>
      WHERE t1.Date_of_data > t2.Date_of_data AND Status IS NOT NULL<br>
      ORDER BY Date_of_data DESC) AS t3(prevStatus)<br>
    ORDER BY 1,2<br></p>
  </blockquote>