我有一个查询,使用一个CASE
语句来记分帐户.查询查找矢量形式的值.因此,例如,如果我是一名患者,我可以拥有多个诊断代码,但它们不会存储为列值,而是存储在另一行中,如下所示:
VISIT_ID | CLASFCD 123 | 196.0 123 | 197.0 123 | 198.0 321 | 199.0 321 | 650.9 222 | 111 555 | ... ...
我的查询使用了一个Case Statment,如下所示:
, 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
谢谢您的帮助,
问题是您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
子查询中可能没有必要,但这取决于您的数据真实情况.