title: SQL Server Case Statement- Deriving one column from two others when grouping by transactions
body: <p>I am looking to apply make a flag based on the below scenario. The source table and desired results I am after are in the tables below. </p>
  
  <p>When <code>SRCE_SYST_FLAG</code> isn't null (and <code>= Y</code>), then create a new flag (<code>DERIVED_FLAG</code>) using the value in the <code>SRCE_SYST_FLAG</code> column, else use the <code>GENERIC_FLAG</code> column. </p>
  
  <p>However if another record in the same <code>TRAN_ID</code> group has the <code>SRCE_SYST_FLAG</code> column populated, set the <code>DERIVED_FLAG</code> to the other <code>SRCE_SYST_F</code>. This is the case in <code>TRAN_ID = 2222</code> below. </p>
  
  <p>Would anyone know how to do this using a case statement? I can make a generic one such as the one below, but this doesn't use the <code>SRCE_SYST_FLAG</code> for the records that are NULL but the <code>SRCE_SYST_FLAG</code> is populated for another record in that tran_id group  :</p>
  
  <pre><code>CASE
     WHEN SRCE_SYST_Flag = 'Y' 
        THEN SRCE_SYST_FLAG 
        ELSE GENERIC_FLAG
  END AS 'DERIVED_FLAG' 
  </code></pre>
  
  <p>Sample data set:</p>
  
  <pre><code>Client | Limit        | Pdct_type_c |  SRCE_SYST_FLAG | GENERIC_FLAG |  TRAN_ID 
  --------------------------------------------------------------------------------
  John   | 4,000,000.00 | DCO         |  y              | N            | 2222
  John   |              | DCO         |  NULL           | N            | 2222
  Mark   | 2,000,000.00 | DCO         |  NULL           | N            | 435
  Mark   |              | DCO         |  NULL           | N            | 435
  Luke   | 4,000,000.00 | DCO         |  N              | Y            | 980
  </code></pre>
  
  <p>Desired result:</p>
  
  <pre><code>Client | Limit        | Pdct_type_c | DERIVED_FLAG |  TRAN_ID 
  ---------------------------------------------------------------
  John   | 4,000,000.00 | DCO         | Y            | 2222
  John   |              | DCO         | Y            | 2222
  Mark   | 2,000,000.00 | DCO         | N            | 435
  Mark   |              | DCO         | N            | 435
  Luke   | 4,000,000.00 | DCO         | Y            | 980
  </code></pre>
  
  <p>I appreciate any assistance,</p>
  
  <p>Cheers</p>