title: SQL working days query
body: <p>I am trying to write a query to calculate the number of working days between 2 dates. I first tried this in VBA, which worked, but this is not very efficient.</p>
  
  <p>I have 2 queries, the first works out the date difference between a valueDate and cutOffDate; the second counts the number of dates from a table of holidays/weekends that fall between the valueDate and cutOffDay.</p>
  
  <p>The trouble I'm having is how to combine these 2 parts to give the item age (number of working days between the dates).</p>
  
  <p>My query examples are:</p>
  
  <pre><code>SELECT allOS.SortCode, allOS.NPA, allOS.valueDate, allOS.cutOffDate, 
  DateDiff("d",[allOS.valueDate],[allOS.cutOffDate]) AS Age
  FROM allOS;
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT Count(Holidays.Holiday) AS NonWorkingDays 
  FROM Holidays 
  HAVING (([Holiday]&gt;[#01/01/2013#] And [Holiday]&lt;[#11/06/2013#]));   
  </code></pre>
  
  <p>I need to subtract the result of the second query from the Age of the first query.</p>
  
  <p>Sample input and output data</p>
  
  <p>allOS:</p>
  
  <pre><code>sortCode|npa|valueDate|cutOffDate
  111111|99999999|01-11-2013|15-11-2013
  222222|77777777|04-11-2013|15-11-2013
  333333|88888888|05-11-2013|15-11-2013
  444444|66666666|06-11-2013|15-11-2013
  555555|44444444|07-11-2013|15-11-2013
  666666|33333333|12-11-2013|15-11-2013
  777777|55555555|13-11-2013|15-11-2013
  888888|11111111|14-11-2013|15-11-2013
  999999|22222222|15-11-2013|15-11-2013
  </code></pre>
  
  <p>Holidays:</p>
  
  <pre><code>holiday|reason
  02-11-2013|Saturday
  03-11-2013|Sunday
  08-11-2013|Long Weekend
  09-11-2013|Saturday
  10-11-2013|Sunday
  11-11-2013|Long Weekend
  16-11-2013|Saturday
  17-11-2013|Sunday`
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>sortCode|npa|valueDate|cutOffDate|Age
  111111|99999999|01-11-2013|15-11-2013|8
  222222|77777777|04-11-2013|15-11-2013|7
  333333|88888888|05-11-2013|15-11-2013|6
  444444|66666666|06-11-2013|15-11-2013|5
  555555|44444444|07-11-2013|15-11-2013|4
  666666|33333333|12-11-2013|15-11-2013|3
  777777|55555555|13-11-2013|15-11-2013|2
  888888|11111111|14-11-2013|15-11-2013|1
  999999|22222222|15-11-2013|15-11-2013|0
  </code></pre>
  
  <p>The results for age is the difference between the valueDate and cutOffDate less any of the days from the holiday table.</p>