sql - 使用 CASE 语句选择 SQL Server 2008 R2

Question

我有一个使用 CASE 语句对帐户进行评分的查询。该查询查找向量形式的值。因此，例如，如果我是一名患者，我可以有多个诊断代码，但它们不存储为列值，它们存储在另一行中，如下所示:

VISIT_ID | CLASFCD
123      | 196.0
123      | 197.0
123      | 198.0
321      | 199.0
321      | 650.9
222      | 111
555      | ...
...

我的查询使用了这样的案例陈述:

, 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

我为 5 组不同的代码执行此操作。发生的情况是，如果其中一个组满足标准，则结果将返回另一行而不是同一行。这是我返回的数据示例:

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

我想要返回的内容如下:

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

最终总分不能超过6。

完整的查询在这里，它是一个多部分查询的一部分，我正在对原来的进行更改:

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) >= 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

感谢您的帮助，

Answer 1

问题是您将计算的 PRIN_DX_ 列包含在聚合中。相反，将它们从聚合中删除并选择非 0 值(使用 max()):

SELECT C.PT_NO, C.MED_REC_NO, C.PT_NAME,
       max(C.PRIN_DX_CD_1) as PRIN_DX_CD_1,
       max(C.PRIN_DX_CD_2) as PRIN_DX_CD_2,
       max(C.PRIN_DX_CD_3) as PRIN_DX_CD_3,
       max(C.PRIN_DX_CD_4) as PRIN_DX_CD_4,
       max(C.PRIN_DX_CD_5) as PRIN_DX_CD_5,
       (case when max(C.PRIN_DX_CD_1) + max(C.PRIN_DX_CD_2) + max(C.PRIN_DX_CD_3) + 
                  max(C.PRIN_DX_CD_4) + max(C.PRIN_DX_CD_5) < 6
             then max(C.PRIN_DX_CD_1) + max(C.PRIN_DX_CD_2) + max(C.PRIN_DX_CD_3) + 
                  max(C.PRIN_DX_CD_4) + max(C.PRIN_DX_CD_5)
             else 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
ORDER BY C.Pt_No;

我怀疑子查询中的 distinct 可能不是必需的，但这取决于您的数据的真实情况。