如果 dis_stat = 20，SQL 如何获取以前的记录

Question

我正在尝试获取记录，如果它们满足特定条件则不会包含在记录中。我可以获得我需要的所有记录，但是当我尝试根据试图消除该记录组合来对记录进行分组时，我不确定该怎么做。如果 dis_stat = 20 则不显示该记录和以前的记录。示例记录:

MEMBER : Readmit_conf_30 : Conf_num : Dis_Stat
12345                         1212       6
12345                         1213       6
12345      1215               1214       1
12345      1216               1215       7
12345                         1216       20

因此 conf_num = 1216 和 dis_stat = 20 不应与之前的 conf_num= 1215、readmit_conf_30 = 1216 记录一起显示

这是我必须获取该成员记录的代码。我尝试了很多方法来摆脱这 2 条记录，但没有成功。

感谢您的帮助。

代码:

WITH Group1 AS (
SELECT A.MEMBER, B.SEX, B.DOB, B.LAST_NAME, B.FIRST_NAME, A.PROVIDER_ID, C.PROVIDER_NAME
, A.CONF_NUM, A.BEG_DT, A.END_DT, A.TOS_I_4, A.DIS_STAT, A.AMT_EQV,  A.AMT_PAY, A.AMT_REQ
, A.READMIT_INDEX_30, A.READMIT_30, A.READMIT_CONF_30
FROM CONFINEMENTS A 
LEFT JOIN MEMINFO B ON A.MEMBER = B.MEMBER 
LEFT JOIN PROVINFO C ON A.PROVIDER_ID = C.PROVIDER_ID
LEFT JOIN MAP_DATE_RANGE D ON D.IA_TIME = A.IA_TIME
WHERE A.CUST_OPL_MED IN ('A$*', 'B$*', 'N$*', 'P$*', 'D$*',  'Unspecified$UNK')
--AND A.BEG_DT >= '05/01/2013' and A.beg_dt <'05/01/2014' 
and A.PROVIDER_ID  Like '344428218046' --firelands regional 
and (A.READMIT_30 = 1 OR A.READMIT_30 = 0)
--AND A.DIS_STAT <> 20
AND A.MEMBER = '03732529500'  --'04644786700'
),
GROUP2 AS (
SELECT AA.MEMBER, AA.READMIT_CONF_30
FROM Group1 AA
WHERE AA.READMIT_CONF_30 > 0
--AND AA.DIS_STAT <> 20
),
select MEMBER, READMIT_CONF_30
,SEX, DOB, LAST_NAME, FIRST_NAME, PROVIDER_ID, PROVIDER_NAME
, CONF_NUM, BEG_DT, END_DT, TOS_I_4, DIS_STAT, AMT_EQV, AMT_PAY, AMT_REQ
, READMIT_30, READMIT_INDEX_30
from group1
union 
select MEMBER, READMIT_CONF_30
,SEX, DOB, LAST_NAME, FIRST_NAME, PROVIDER_ID, PROVIDER_NAME
, CONF_NUM, BEG_DT, END_DT, TOS_I_4, DIS_STAT, AMT_EQV, AMT_PAY, AMT_REQ
, READMIT_30, READMIT_INDEX_30
from Group3
order by conf_num

Answer 1

您应该能够使用 NOT EXISTS().. 尽管使用您提供的代码示例很难准确判断位置.. 但在您的第一个 cte GROUP1 中它看起来像

WHERE 
    ----
    AND a.Dis_Stat <> 20
    AND NOT EXISTS (SELECT 1 
                    FROM CONFINEMENTS c 
                    WHERE c.Conf_num = a.Readmit_conf_30 
                          AND c.Dis_Stat = 20)