title: How to set a value through a Case statement for each row
body: <p>Good day. I have a script that looks at a table and performs some logic against a column <code>[vCheck]</code>. The logic is not working correctly. Table A is pasted below. </p>
  
  <p>If a condition is true, the statement is supposed to set that associated row  to 0. It currently sets all associated rows of that same <code>typeCol</code> to 0. How do I individualize each rows' condition with respect to the groupid in order to have unique values for <code>vCheck</code>? I tried using the cursor method, IF's, while, table variables, and update and that led to the same issue. </p>
  
  <p>Table A</p>
  
  <pre><code>groupid eventid numid   tagVal  greaterThan greaterEqThan   lessThan    lessEqThan  equalTo typeCol vCheck  activeid    actVal
  28      18      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          0
  28      18      9000001 NULL    NULL        NULL            105         NULL        NULL    3       1       68          74.9101
  28      18      9001767 NULL    NULL        NULL            10          NULL        NULL    3       1       69          9.95535
  9       19      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          0
  9       19      9000001 NULL    NULL        NULL            105         NULL        NULL    3       1       68          74.9101
  9       19      9001767 NULL    10          NULL            NULL        NULL        NULL    1       1       69          9.95535
  10      20      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          0
  10      20      9000001 NULL    105         NULL            NULL        NULL        NULL    1       1       68          74.9101
  10      20      9001767 NULL    10          NULL            NULL        NULL        NULL    1       1       69          9.95535
  </code></pre>
  
  <p>The script </p>
  
  <pre><code>DECLARE @nID nvarchar(50)
  SET @nID = '9000001'
  
  /****************Get all associated eventids with respect to the incoming numid**/
  ;WITH ss
  AS (SELECT
    a.groupid,
    a.eventid
  FROM subobjgroup a
  WHERE numid = @nID)
  /****************Get each member of the eventids selected above along with the tag data type, and tagval for each member.*/
  ,
  ss2
  AS (SELECT
    c.*
  
  FROM ss b
  INNER JOIN subobjgroup c
    ON b.eventid = c.eventid
   )
  
  /**************Check if each member in the groupid and their conditions to each eventid selected are met*/
  SELECT
    g.groupid,
    g.eventid,
    g.numid,
    g.tagVal,
    g.greaterThan,
    g.greaterEqThan,
    g.lessThan,
    g.lessEqThan,
    g.equalTo,
    g.typeCol,
    vCheck =
            CASE
              WHEN g.typecol = 0 THEN CASE
                  WHEN g.actval = g.tagval THEN 0
                  ELSE 1
                END
              WHEN g.typecol = 1 THEN CASE
                  WHEN g.actval &gt; g.greaterthan THEN 0
                  ELSE 1
                END
              WHEN g.typecol = 2 THEN CASE
                  WHEN g.actval &gt;= g.greatereqthan THEN 0
                  ELSE 1
                END
              WHEN g.typecol = 3 THEN CASE
                  WHEN g.actval &lt; g.lessthan THEN 0
                  ELSE 1
                END
              WHEN g.typecol = 4 THEN CASE
                  WHEN g.actval &lt;= g.lesseqthan THEN 0
                  ELSE 1
                END
              WHEN g.typecol = 5 THEN CASE
                  WHEN g.actval = g.equalto THEN 0
                  ELSE 1
                END
              ELSE 0
            END,
    g.activeid,
    g.actVal
  
  FROM ss2 g
  ORDER BY g.eventid
  </code></pre>
  
  <p>The results... (keep in mind, the last column actVal constantly changes. You will see the vCheck column have values other than 1. Notice all vCheck values are linked by the same typeCol value instead of uniquely checking each rows condition)</p>
  
  <pre><code>groupid eventid numid   tagVal  greaterThan greaterEqThan   lessThan    lessEqThan  equalTo typeCol vCheck  activeid    actVal
  9       19      9000001 NULL    NULL        NULL            105         NULL        NULL    3       1       68          96.7658
  9       19      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          1
  9       19      9001767 NULL    10          NULL            NULL        NULL        NULL    1       0       69          10.9553
  10      20      9000001 NULL    105         NULL            NULL        NULL        NULL    1       0       68          96.7658
  10      20      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          1
  10      20      9001767 NULL    10          NULL            NULL        NULL        NULL    1       0       69          10.9553
  28      18      9000001 NULL    NULL        NULL            105         NULL        NULL    3       1       68          96.7658
  28      18      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          1
  28      18      9001767 NULL    NULL        NULL            10          NULL        NULL    3       1       69          10.9553
  </code></pre>
  
  <p>The desired results.... (notice vCheck column and the 1st, 4th, and 7th row. They are changed based on the condition and that row got effected and was set to 0 or 1)</p>
  
  <pre><code>groupid eventid numid   tagVal  greaterThan greaterEqThan   lessThan    lessEqThan  equalTo typeCol vCheck  activeid    actVal
  9       19      9000001 NULL    NULL        NULL            105         NULL        NULL    3       0       68          96.7658
  9       19      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          1
  9       19      9001767 NULL    10          NULL            NULL        NULL        NULL    1       0       69          10.9553
  10      20      9000001 NULL    105         NULL            NULL        NULL        NULL    1       1       68          96.7658
  10      20      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          1
  10      20      9001767 NULL    10          NULL            NULL        NULL        NULL    1       0       69          10.9553
  28      18      9000001 NULL    NULL        NULL            105         NULL        NULL    3       0       68          96.7658
  28      18      9000002 0       NULL        NULL            NULL        NULL        NULL    0       1       70          1
  28      18      9001767 NULL    NULL        NULL            10          NULL        NULL    3       1       69          10.9553
  </code></pre>