title: Select with CASE Statement SQL Server 2008 R2
body: <p>I have a query that uses a <code>CASE</code> statement to score accounts. The query looks for values that are in a vector form. So for instance if I am a patient I can have multiple diagnosis codes, but they are not stored as column values, they are stored in another row like so:</p>
  
  <pre><code>VISIT_ID | CLASFCD
  123      | 196.0
  123      | 197.0
  123      | 198.0
  321      | 199.0
  321      | 650.9
  222      | 111
  555      | ...
  ...
  </code></pre>
  
  <p>My query uses a Case Statment like so:</p>
  
  <pre><code>, CASE
      WHEN DV.ClasfCd IN (
      '196.0','196.1','196.2','196.3','196.5','196.6','196.8','196.9',
      '197.0','197.1','197.2','197.3','197.4','197.5','197.6','197.7',
      '197.8','198.2','198.3','198.4','198.5','199.1','209.7'
      )
      THEN 6
      ELSE 0
    END AS PRIN_DX_CD_5
  </code></pre>
  
  <p>I do this for 5 different groups of codes. What's happening is that if the criteria is met for one of those groups, the results come back in another row instead of on the same row.  Here is an example of the data that I am getting back:</p>
  
  <pre><code>VISIT_ID | CC GROUP 1 | CC GROUP 2 | CC GROUP 3 | CC GROUP 4 | CC GROUP 5 | TOTAL
  123      | 1          | 0          | 0          | 0          | 0          | 1
  123      | 0          | 2          | 0          | 0          | 0          | 2
  123      | 0          | 0          | 0          | 0          | 0          | 0
  </code></pre>
  
  <p>What I want returned is the following:</p>
  
  <pre><code>VISIT_ID | CC GROUP 1 | CC GROUP 2 | CC GROUP 3 | CC GROUP 4 | CC GROUP 5 | TOTAL
  123      | 1          | 2          | 0          | 0          | 0          | 3
  321      | 1          | 0          | 0          | 0          | 6          | 6
  </code></pre>
  
  <p><em><strong>The final total score cannot exceed 6.</em></strong></p>
  
  <p>The entire query in some brevity is here, it is part of a multi-part query, I am making changes to the original:</p>
  
  <pre><code>SET ANSI_NULLS OFF
  GO
  DECLARE @SD DATETIME
  DECLARE @ED DATETIME
  SET @SD = '2013-01-01';
  SET @ED = '2013-05-31';
  
  -- @CM TABLE DECLARATION #############################################]
  DECLARE @CM TABLE (
  ENCOUNTER_ID VARCHAR(200)
  , [MRN CM] VARCHAR(200)
  , NAME VARCHAR(500)
  , [CC GRP ONE SCORE] VARCHAR(20)
  , [CC GRP TWO SCORE] VARCHAR(20)
  , [CC GRP THREE SCORE] VARCHAR(20)
  , [CC GRP FOUR SCORE] VARCHAR(20)
  , [CC GRP FIVE SCORE] VARCHAR(20)
  , [CC LACE SCORE] INT
  )
  --####################################################################]
  
  INSERT INTO @CM
  SELECT
  C.PT_NO
  , C.MED_REC_NO
  , C.PT_NAME
  , C.PRIN_DX_CD_1
  , C.PRIN_DX_CD_2
  , C.PRIN_DX_CD_3
  , C.PRIN_DX_CD_4
  , C.PRIN_DX_CD_5
  , CASE
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 0 THEN 0
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 1 THEN 1
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 2 THEN 2
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 3 THEN 3
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 4 THEN 4
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) = 5 THEN 5
      WHEN (C.PRIN_DX_CD_1+C.PRIN_DX_CD_2+C.PRIN_DX_CD_3+C.PRIN_DX_CD_4+C.PRIN_DX_CD_5) &gt;= 6 THEN 6
    END AS CC_LACE_SCORE
  
  FROM (
      SELECT DISTINCT PAV.PT_NO
      , MED_REC_NO
      , PT_NAME
      , CASE
          WHEN dv.ClasfCd IN (
  
          )
          THEN 1
          ELSE 0
        END AS PRIN_DX_CD_1
      , CASE
          WHEN DV.ClasfCd IN (
  
          )
          THEN 2
          ELSE 0
      END AS PRIN_DX_CD_2
      , CASE
          WHEN DV.ClasfCd IN (
  
          )
          THEN 3
          ELSE 0
        END AS PRIN_DX_CD_3
      , CASE
          WHEN DV.ClasfCd IN (
  
          )
          THEN 4
          ELSE 0
        END AS PRIN_DX_CD_4
      , CASE
          WHEN DV.ClasfCd IN (
  
          )
          THEN 6
          ELSE 0
        END AS PRIN_DX_CD_5
  
        FROM smsdss.BMH_PLM_PtAcct_V PAV
        JOIN smsdss.BMH_PLM_PtAcct_Clasf_Dx_V DV
        ON PAV.PtNo_Num = DV.PtNo_Num
  
        WHERE Dsch_Date BETWEEN @SD AND @ED
  
  
  )C
  
  GROUP BY C.PT_NO
  , C.MED_REC_NO
  , C.PT_NAME
  , C.PRIN_DX_CD_1
  , C.PRIN_DX_CD_2
  , C.PRIN_DX_CD_3
  , C.PRIN_DX_CD_4
  , C.PRIN_DX_CD_5
  ORDER BY C.Pt_No
  
  SELECT * FROM @CM
  </code></pre>
  
  <p>thank you for your help,</p>