title: Need to expire parent record if all child records are expired- SQL
body: <p>I have a table JOB_DISPPLAY as under:</p>
  
  <pre><code>JOB_STS    JOB NAME    FREQUENCY    CHILD ID   START       END_DATE
   R         JOB001       Daily      
   E         JOB001                    CH001    01-01-2012  12-15-2016
   R         JOB001                    CH002    06-12-2016  01-01-2049
  
   F         JOB002       Montly       
   E         JOB002                    CH003    06-12-2014  01-01-2016
   E         JOB002                    CH004    03-10-2015  01-01-2016
  
   R         JOB003       Montly       
   F         JOB003                    CH006    06-12-2014  01-01-2018
   F         JOB003                    CH007    03-10-2015  01-01-2018
  </code></pre>
  
  <p>Here JOB001 is parent and it has 2 childs- CH001 and CH002. Similarly JOB002 is parent and has 2 child CH003 and CH004. Job STatus R=Running, F=Finished and E=Expired</p>
  
  <p>Now i want to update the table for parent record sts such that if ALL child have column end_date in past compared to today i.e if the child records are expired (job_sts =E) then parent record should also expire.</p>
  
  <p>Hence my end result should be with JOB002 whole set expired like:</p>
  
  <pre><code>JOB_STS    JOB NAME    FREQUENCY    CHILD ID   START       END_DATE
   R         JOB001       Daily      
   E         JOB001                    CH001    01-01-2012  12-15-2016
   R         JOB001                    CH002    06-12-2016  01-01-2049
  
   E         JOB002       Montly       
   E                                   CH003    06-12-2014  01-01-2016
   E                                   CH004    03-10-2015  01-01-2016
  
   R         JOB003       Montly       
   F                                   CH006    06-12-2014  01-01-2018
   F                                   CH007    03-10-2015  01-01-2018
  </code></pre>
  
  <p>How can i achieve this?</p>
  
  <p>Thanks!</p>