title: Add N business days to a given date skipping holidays, exceptions and weekends in SQL DB2
body: <p>I'm facing a challenging task here, spent a day on it and <strong>I was only able to solve it through a procedure</strong> but it is taking too long to run for all projects.</p>
  
  <p>I would like to solve it in a single query if possible (no functions or procedures).</p>
  
  <p>There is already some questions here doing it in programming languages OR sql functions/procedures (Wich I also solved min). So I'm asking if it is possible to solve it with just SQL</p>
  
  <p>The background info is: </p>
  
  <ul>
  <li>A <code>project</code> table</li>
  <li>A <code>phase</code> table</li>
  <li>A <code>holiday</code> table</li>
  <li>A <code>dayexception</code> table which cancel a holiday or a weekend day (make that date as a working day) and it is associated with a project </li>
  <li>A project may have 0-N phases</li>
  <li>A phase have a <code>start date</code>, a <code>duration</code> and a <code>draworder</code> (needed by the system)</li>
  <li>Working days is all days that is not weekend days and not a holiday (exception is if that date is in <code>dayexception</code> table)</li>
  </ul>
  
  <p>Consider this following scenario:</p>
  
  <pre><code>project |           phase(s)                        |  Dayexception     |  Holiday
    id    | id   pid    start     duration  draworder | pid       date    |   date
     1    | 1     1   2014-01-20     10         0     |  1     2014-01-25 | 2014-01-25
          | 2     1   2014-02-17     14         2     |                   |    
  </code></pre>
  
  <p>The <code>ENDDATE</code> for the <code>project id 1</code> and <code>phase id 1</code> is actually <code>2014-01-31</code> see the generated data below:
  The date on the below data (and now on) is formatted as <code>dd/mm/yyyy</code> (Brazil format) and the value <code>N</code> is <code>null</code></p>
  
  <pre><code>proj  pha     start          day      weekday  dayexcp      holiday  workday
   1     1    20/01/2014    20/01/2014     2        N            N        1
   1     1    20/01/2014    21/01/2014     3        N            N        1
   1     1    20/01/2014    22/01/2014     4        N            N        1
   1     1    20/01/2014    23/01/2014     5        N            N        1
   1     1    20/01/2014    24/01/2014     6        N            N        1
   1     1    20/01/2014    25/01/2014     7    25/01/2014   25/01/2014   1
   1     1    20/01/2014    26/01/2014     1        N            N        0
   1     1    20/01/2014    27/01/2014     2        N        27/01/2014   0
   1     1    20/01/2014    28/01/2014     3        N            N        1
   1     1    20/01/2014    29/01/2014     4        N            N        1
  </code></pre>
  
  <p>To generate the above data I created a view <code>daysOfYear</code> with all days from 2014 and 2015 (it can be bigger or smaller, created it with two years for the year turn cases) with a CTE query if you guys want to see it let me know and I will add it here. And the following select statement:</p>
  
  <pre><code>select ph.project_id proj, 
         ph.id phase_id pha,
         ph.start,
         dy.curday day,
         dy.weekday, /*weekday here is a calling to the weekday function of db2*/
         doe.exceptiondate dayexcp,
         h.date holiday,
         case when exceptiondate is not null or (weekday not in (1,7) and h.date is null)
              then 1 else 0 end as workday
    from phase ph
         inner join daysofyear dy
            on (year(ph.start) = dy.year)
         left join dayexception doe
            on (ph.project_id = doe.project_id
                and dy.curday = truncate(doe.exceptiondate))
         left join holiday h
            on (dy.curday = truncate(h.date))
   where ph.project_id = 1
     and ph.id = 1
     and dy.year in (year(ph.start),year(ph.start)+1)
     and dy.curday&gt;=ph.start
     and dy.curday&lt;=ph.start + ((duration - 1) days)
     order by ph.project_id, start, dy.curday, draworder
  </code></pre>
  
  <p>To solve this scenario I created the following query:</p>
  
  <pre><code>select project_id, 
         min(start), 
         max(day) + sum(case when workday=0 then 1 else 0 end) days as enddate
    from project_phase_days /*(view to the above select)*/
  </code></pre>
  
  <p>This will return correctly:</p>
  
  <pre><code>proj     start           enddate
   1     20/01/2014      31/01/2014
  </code></pre>
  
  <p>The problem I couldn't solve is if the days I'm adding (non workdays <code>sum(case when workday=0 then 1 else 0 end) days</code> ) to the last enddate (<code>max(day)</code>) is weekend days or holidays or exceptions. </p>
  
  <p>See the following scenario (<strong>The duration for the below phase is 7</strong>):</p>
  
  <pre><code>proj   pha     start          day      weekday  dayexcp   holiday  workday
   81    578    14/04/2014    14/04/2014     2        N        N        1
   81    578    14/04/2014    15/04/2014     3        N        N        1
   81    578    14/04/2014    16/04/2014     4        N        N        1
   81    578    14/04/2014    17/04/2014     5        N        N        1
   81    578    14/04/2014    18/04/2014     6        N    18/04/2014   0
   81    578    14/04/2014    19/04/2014     7        N                 0
   81    578    14/04/2014    20/04/2014     1        N    20/04/2014   0
             /*the below data I added to show the problem*/              
   81    578    14/04/2014    21/04/2014     2        N    21/04/2014   0
   81    578    14/04/2014    22/04/2014     3        N                 1
   81    578    14/04/2014    23/04/2014     4        N                 1
   81    578    14/04/2014    24/04/2014     5        N                 1 
  </code></pre>
  
  <p>With the above data my query will return</p>
  
  <pre><code>proj     start       enddate
   81    14/04/2014   23/04/2014
  </code></pre>
  
  <p>But the correct result would be the <code>enddate</code> as <code>24/04/2014</code> that's because my query doesn't take into account if the days after the last day is weekend days or holidays (or exceptions for that matter) as you can see in the dataset above the day <code>21/04/2014</code> which is outside my duration is also a Holiday.</p>
  
  <p>I also tried to create a CTE on <code>phase</code> table to add a day for each iteration until the duration is over but I couldn't add the <code>exceptions</code> nor the <code>holidays</code> because the DB2 won't let me add a left join on the CTE recursion. Like this:</p>
  
  <pre><code> with CTE (projectid, start, enddate, duration, level) as (
       select projectid, start, start as enddate, duration, 1
         from phase 
        where project_id=1
          and phase_id=1
        UNION ALL
       select projectid, start, enddate + (level days), duration, 
              case when isWorkDay(enddate + (level days)) then level+1 else level end as level
         from CTE left join dayexception on ...
                  left join holiday on ...
        where level &lt; duration
   ) select * from CTE
  </code></pre>
  
  <p>PS: the above query doesn't work because of the DB2 limitations and <code>isWorkDay</code> is just as example (it would be a case on the dayexception and holiday table values).</p>
  
  <p>If you have any doubts, please just ask in the comments.
   Any help would be greatly appreciated. Thanks.</p>