title: Finding fields to update based on combinations
body: <p>I need to be able to display in my results who needs updates.  I have a temp table I created that looks like this. The rule is per ID they cannot have more than 1 MASTER = 1.  They must have FULLTIME = 1 on that record and all other records will be FULLTIME = 0 and PARTTIME = 1.  This is quite difficult because you have to compare across multiple IDs.</p>
  
  <p>I've tried combinations using maxes, count distinct, subqueries, etc.  No luck getting it done.  I've even tried to do some manipulation in Excel but it's totally confusing to me.</p>
  
  <pre><code>select distinct
  x.ID,
  COUNT(x.ID) AS ID_Count
  from #FT0PT1M1Version2 as x
  join (
  
  select 
  ID, NAME, MASTER, FULLTIME, PARTTIME 
  from #FT0PT1M1Version2
  WHERE E = 'P'
  GROUP BY 
  ID, NAME, MASTER, FULLTIME, PARTTIME 
  HAVING COUNT(ID) = '1'
  
  ) as y
      on x.ID = y.ID   
      WHERE 
      x.PARTTIME = '1' and
      x.MASTER = '1'
  
      group by x.ID
      HAVING COUNT(x.ID) = '1'
      order by 1
  </code></pre>
  
  <p>Temp Table</p>
  
  <pre><code>ID  NAME            MASTER  FULLTIME    PARTTIME
  1   JAMES JONES     0       1              0
  1   JAMES JONES     1       0              1
  1   JAMES JONES     0       0              1
  2   MICHEAL JORDAN  1       1              0
  2   MICHEAL JORDAN  0       0              1
  2   MICHEAL JORDAN  0       0              1
  3   JOHN DOE        1       1              0
  3   JOHN DOE        0       0              1
  </code></pre>
  
  <p>Expected Results</p>
  
  <pre><code>ID  NAME            MASTER  FULLTIME  PARTTIME  UPDATE
  1   JAMES JONES     0       1         0         Y
  1   JAMES JONES     1       0         1         Y
  1   JAMES JONES     0       0         1         N
  2   MICHEAL JORDAN  1       1         0         N
  2   MICHEAL JORDAN  0       0         1         N
  2   MICHEAL JORDAN  0       0         1         N
  3   JOHN DOE        1       1         0         N
  3   JOHN DOE        1       0         1         Y
  </code></pre>