CODEWITHSUNDEEP
sqlsql-serversql-server-2008
arios
arios

Reputation: 185

How to set a value through a Case statement for each row

Good day. I have a script that looks at a table and performs some logic against a column [vCheck]. The logic is not working correctly. Table A is pasted below.

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 typeCol to 0. How do I individualize each rows' condition with respect to the groupid in order to have unique values for vCheck? I tried using the cursor method, IF's, while, table variables, and update and that led to the same issue.

Table A

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

The script 

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 > g.greaterthan THEN 0
                ELSE 1
              END
            WHEN g.typecol = 2 THEN CASE
                WHEN g.actval >= g.greatereqthan THEN 0
                ELSE 1
              END
            WHEN g.typecol = 3 THEN CASE
                WHEN g.actval < g.lessthan THEN 0
                ELSE 1
              END
            WHEN g.typecol = 4 THEN CASE
                WHEN g.actval <= 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

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)

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

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)

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

Upvotes: 0

Views: 1785

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

I used this:

with subobjgroup(groupid ,eventid ,numid ,tagVal ,greaterThan ,greaterEqThan ,lessThan ,lessEqThan ,equalTo ,typeCol ,vCheck ,activeid ,actVal) as
    (  select 28,18,9000002,0,NULL,NULL,NULL,NULL,NULL,0,1,70,0 union all
       select 28,18,9000001,NULL,NULL,NULL,105,NULL,NULL,3,1,68,74.9101 union all
       select 28,18,9001767,NULL,NULL,NULL,10,NULL,NULL,3,1,69,9.95535 union all
       select 9,19,9000002,0,NULL,NULL,NULL,NULL,NULL,0,1,70,0 union all
       select 9,19,9000001,NULL,NULL,NULL,105,NULL,NULL,3,1,68,74.9101 union all
       select 9,19,9001767,NULL,10,NULL,NULL,NULL,NULL,1,1,69,9.95535 union all
       select 10,20,9000002,0,NULL,NULL,NULL,NULL,NULL,0,1,70,0 union all
       select 10,20,9000001,NULL,105,NULL,NULL,NULL,NULL,1,1,68,74.9101 union all
       select 10,20,9001767,NULL,10,NULL,NULL,NULL,NULL,1,1,69,9.95535),
    ss AS (SELECT
      a.groupid,
      a.eventid
    FROM subobjgroup a
    WHERE numid = 9000001)
    /****************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 coalesce(g.actval, 0) = coalesce(g.tagval, 0) THEN 0
                    ELSE 1
                  END
                WHEN g.typecol = 1 THEN CASE
                    WHEN g.actval > g.greaterthan THEN 0
                    ELSE 1
                  END
                WHEN g.typecol = 2 THEN CASE
                    WHEN g.actval >= g.greatereqthan THEN 0
                    ELSE 1
                  END
                WHEN g.typecol = 3 THEN CASE
                    WHEN g.actval < g.lessthan THEN 0
                    ELSE 1
                  END
                WHEN g.typecol = 4 THEN CASE
                    WHEN g.actval <= g.lesseqthan THEN 0
                    ELSE 1
                  END
                WHEN g.typecol = 5 THEN CASE
                    WHEN coalesce(g.actval, 0) = coalesce(g.equalto, 0) THEN 0
                    ELSE 1
                  END
                ELSE 0
              END,
      g.activeid,
      g.actVal

    FROM ss2 g
    ORDER BY g.eventid

and correct result based on your data. vCheck is not coming 1 just because typecol is 1, but because data is like so.

groupid eventid numid   tagVal  greaterThan greaterEqThan   lessThan    lessEqThan  equalTo typeCol vCheck  activeid    actVal
28      18      9000002 0       NULL        NULL            NULL        NULL        NULL    0       0       70          0,00000
28      18      9000001 NULL    NULL        NULL            105         NULL        NULL    3       0       68          74,91010
28      18      9001767 NULL    NULL        NULL            10          NULL        NULL    3       0       69          9,95535
9       19      9000002 0       NULL        NULL            NULL        NULL        NULL    0       0       70          0,00000
9       19      9000001 NULL    NULL        NULL            105         NULL        NULL    3       0       68          74,91010
9       19      9001767 NULL    10          NULL            NULL        NULL        NULL    1       1       69          9,95535
10      20      9000002 0       NULL        NULL            NULL        NULL        NULL    0       0       70          0,00000
10      20      9000001 NULL    105         NULL            NULL        NULL        NULL    1       1       68          74,91010
10      20      9001767 NULL    10          NULL            NULL        NULL        NULL    1       1       69          9,95535

Upvotes: 1

Related Questions