title: SQL: flag if value exists in the past
body: <p>I have a table in SQL Server 2012, with columns like:</p>
  
  <pre><code>Client_ID, Sale_Date
  </code></pre>
  
  <p>I need 2 additional flag columns:</p>
  
  <pre><code>FirstPurchase, ActiveWithin90Days
  </code></pre>
  
  <p>"FirstPurchase" shoud take one of these 2 values: <strong>new</strong>, or <strong>old</strong>.<br/>
  <strong>New</strong> means the Client_ID value is identified for the first time given the existing time frame.<br/>
  <strong>Old</strong> means the value has already been identified for the first time in the past.</p>
  
  <p>"ActiveWithin90Days" should take one of these 2 values: <strong>1</strong> or <strong>0</strong>.
  Value <strong>1</strong> means Client_ID value exists in the previous 90 days. Value <strong>0</strong> means it does NOT exist in the past 90 days.</p>
  
  <p>The desired output looks like this:</p>
  
  <pre><code>Client_ID    Sale_Date    FirstPurchase    ActiveWithin90Days
  1           2013-03-01       new              1
  2           2013-04-01       new              1
  1           2013-05-01       old              1
  1           2013-09-01       old              0
  3           2013-10-01       new              1
  </code></pre>