title: SQL Server query for new and repeat orders per month
body: <p>I am working in SQL Server 2008 R2 and having a hard time gathering new vs repeat customer orders.</p>
  
  <p>I have data in this format:</p>
  
  <pre><code>OrderID     OrderDate   Customer    OrderAmount
  -----------------------------------------------
  1           1/1/2017    A           $10
  2           1/2/2017    B           $20
  3           1/3/2017    C           $30
  4           4/1/2017    C           $40
  5           4/2/2017    D           $50
  6           4/3/2017    D           $60
  7           1/6/2018    B           $70
  </code></pre>
  
  <p>Here's what we want:</p>
  
  <ul>
  <li>New defined as: customer has not placed any orders in any prior months.</li>
  <li>Repeat defined as: customer has placed an order in a prior month (even if many years ago).</li>
  </ul>
  
  <p>This means that if a new customer places multiple orders in her first month, they would all be considered "new" customer orders. And orders placed in subsequent months would all be considered "repeat" customer orders.</p>
  
  <p>We want to get New orders (count and sum) and Repeat orders (count and sum) per year, per month:</p>
  
  <pre><code>Year    Month   NewCount        NewSum          RepeatCount         RepeatSum
  -----------------------------------------------------------------------------
  2017    1       3 (A,B,C)       $60 (10+20+30)  0                   $0
  2017    4       2 (D,D)         $110 (50+60)    1 (C)               $40 (40)
  2018    1       0               $0              1 (B)               $70 (70)
  </code></pre>
  
  <p>(<em>The info in () parenthesis is not part of the result; just putting it here for clarity</em>)</p>
  
  <p>The SQL is easy to write for any single given month, but I don't know how to do it when gathering years worth of months at a time...</p>
  
  <p>If there is a month with no orders of any kind then NULL or 0 values for the year:month would be preferred.</p>