title: SQL Server - Get customers with nth order in specific date range
body: <p><strong>I'm tasked with the following:</strong></p>
  
  <ul>
  <li>Select a list of all customers who had their nth order during a certain date range (usually a specific month).</li>
  <li>This list needs to contain: customer id, sum of first n orders</li>
  </ul>
  
  <p><strong>My tables are something like this:</strong></p>
  
  <ul>
  <li>[dbo.customers]: customerID </li>
  <li>[dbo.orders]: orderID, customerID,
  orderDate, orderTotal</li>
  </ul>
  
  <p><strong>Here is what I've tried so far:</strong></p>
  
  <pre><code>-- Let's assume our threshold (n) is 10
  -- Let's assume our date range is April 2013
  -- Get customers that already had n orders before the beginning of the given date range.
  DECLARE @tmpcustomers TABLE (tmpcustomerID varchar(8))
  INSERT INTO
      @tmpcustomers
  SELECT
      c.customerID
  FROM
      orders o
      INNER JOIN customers c ON o.customerID = c.customerID
  WHERE
      o.orderDate &lt; '2013-04-01'
  GROUP BY c.customerID
  HAVING (COUNT(o.orderID) &gt;= 10)
  
  
  -- Now get all customers that have n orders sometime within the given date range
  -- but did not have n orders before the beginning of the given date range.
  SELECT
      a.customerID, SUM(orderTotal) AS firstTenOrderTotal
  SELECT 
      o.customerID, o.orderID, o.orderTotal
  FROM
      orders o
      INNER JOIN customers c ON c.customerID = o.customerID    
  WHERE
      a.customerID NOT IN ( SELECT tmpcustomerID FROM @tmpcustomers )
  AND
      o.orderDate &gt; '2013-04-01'
  AND
      o.orderDate &lt; '2013-05-01'
  GROUP BY c.customerID
      HAVING COUNT(o.orderID) &gt;= 10
  </code></pre>
  
  <p>This seems to work but it's clunky and slow. Another big problem is that the firstTenOrderTotal is actually the SUM of the total amount of orders by the end of the given date range and not necessarily the first 10.</p>
  
  <p>Any suggestions for a better approach would be much appreciated.</p>