title: Conditional logic when grouping in SQL
body: <p>I need to group some records in a SQL Server 2014 table by using some conditional logic based on multiple fields and I am not sure how to do it. I have created a contrived example to explain what I am looking for as far as results.</p>
  
  <p>Here is a table of Patient Procedures:</p>
  
  <pre><code>+----+-------+-----------+---------------+---------------+
  | Id |  Name | Insurance | ProcedureName | ProcedureDate |
  +----+-------+-----------+---------------+---------------+
  |  1 |  Bob  |  Medicaid |   Blood Test  |   2017-06-01  |
  +----+-------+-----------+---------------+---------------+
  |  2 |  Bob  |  Medicaid |   Urinalysis  |   2017-06-03  |
  +----+-------+-----------+---------------+---------------+
  |  3 |  Bob  |  Self Pay |  Liver Biopsy |   2017-05-02  |
  +----+-------+-----------+---------------+---------------+
  |  4 |  Bob  |  Self Pay |    CAT Scan   |   2017-05-07  |
  +----+-------+-----------+---------------+---------------+
  |  5 | Sally |  Medicaid |   Ultrasound  |   2017-07-07  |
  +----+-------+-----------+---------------+---------------+
  |  6 | Sally |  Medicaid |     X-Ray     |   2017-07-12  |
  +----+-------+-----------+---------------+---------------+
  |  7 | Sally |  Medicaid |   Blood Test  |   2017-08-10  |
  +----+-------+-----------+---------------+---------------+
  </code></pre>
  
  <p>I would like to group these records by name such that I only end up with two records. One for Bob and one for Sally. Since the fields <strong>Insurance</strong>, <strong>ProcedureName</strong>, and <strong>ProcedureDate</strong> hold different values I realize that I need to decide which values to use for these fields in the grouping. Here are the rules:</p>
  
  <ol>
  <li>If the Insurance is anything other than Medicaid use the data from the Non-Medicaid record with the most recent ProcedureDate.</li>
  <li>If the insurance is all Medicaid then use the data from the row with the most recent ProcedureDate.</li>
  </ol>
  
  <p>So I would want to end up with the following two records:</p>
  
  <pre><code>+----+-------+-----------+---------------+---------------+
  | Id |  Name | Insurance | ProcedureName | ProcedureDate |
  +----+-------+-----------+---------------+---------------+
  |  4 |  Bob  |  Self Pay |  Liver Biopsy |   2017-05-02  |
  +----+-------+-----------+---------------+---------------+
  |  7 | Sally |  Medicaid |   Blood Test  |   2017-08-10  |
  +----+-------+-----------+---------------+---------------+
  </code></pre>
  
  <p>I created a <a href="http://sqlfiddle.com/#!6/2e59b9/1" rel="nofollow noreferrer">sample SqlFiddle here</a> for this question. How do I go about using conditional logic in the GROUP BY clause to get the answer I want?</p>