title: SELECT Query between dates, only selecting items between start and end fields
body: <p>I have two tables that I will be using for tracking purposes, a <em>Date Table</em> and a <em>Item Table</em>. The <em>Date Table</em> is used to track the start and end dates of a tracked id. The <em>Item Table</em> is the amount of items that are pulled on a specific date for an id. The id is the foreign key between these two tables.</p>
  
  <p>What I want to do, is a sum of the items with a GROUP BY of the id of the items, but only by summing the items based on if the date of the pulled item falls between the start_date and end_date of the tracked id.</p>
  
  <h2>The Date Table</h2>
  
  <pre>id start_date  end_date
  1   2014-01-01  NULL
  2   2014-01-01  2014-01-02
  3   2014-01-25  NULL</pre>
  
  <h2>The Item Table</h2>
  
  <pre>
  id  items   date
  1   3   2014-01-01
  1   5   2014-01-02
  1   5   2014-01-26
  2   2   2014-01-01
  2   3   2014-01-05
  2   2   2014-01-26
  3   2   2014-01-01
  3   3   2014-01-05
  3   2   2014-01-26
  </pre>
  
  <p>SQL I have so far, but I'm lost as to what to add to it from here.</p>
  
  <pre><code>SELECT 
      a.id, 
      SUM(items) 
  FROM 
      ww_test.dbo.items a
  INNER JOIN ww_test.dbo.dates b ON
      a.id = b.id
  WHERE
      a.date &gt;= '2014-01-01' AND a.date &lt;= '2014-01-30'
  GROUP BY 
      a.id
  ORDER BY 
      a.id
  </code></pre>
  
  <h2>The output should be:</h2>
  
  <pre>
  id  items
  1   13
  2   2
  3   2
  </pre>
  
  <h2>Instead of:</h2>
  
  <pre>
  id  items
  1   13
  2   7
  3   7
  </pre>