首先,这是我的代码:
SELECT --PAT.V51_PRSNPERSONNUMBER AS PSNNMBR,
(PAT.V51_PRSNFIRSTNAME + ' ' + PAT.V51_PRSNLASTNAME) AS NAME,
--ADDR.V8_PERSONHOMEADDRLINE1 AS ADDRESS,
--ADDR.V8_PERSONHOMEADDRLINE2 AS ADDRESS2,
--ADDR.V8_PERSONHOMEADDRCITY AS CITY,
--ADDR.V8_PERSONHOMEADDRSTATE AS STATE,
--ADDR.V8_PERSONHOMEADDRZIP AS ZIP,
--PAT.V51_PRSNDOB AS DOB,
--PAT.V51_PRSNSEX AS SEX,
ADDR.V8_PERSONHOMEPHONENUMBER AS PHONE,
ADDR.V8_PERSONMOBILEPHONENUMBER AS CELLPHONE,
--CV.V9_DNPLANSORTNAME AS INSPLAN,
--CV.V9_DNCARRIERNAME AS INSCARRIER,
PAT.V51_PRSNEMAIL AS EMAIL,
'RSQ TEST' AS GROUPS
FROM I_DNCHGSPMTS CP
LEFT OUTER JOIN IPROCEDURE PR
ON (CP.DNCP_PROCEDURECODE = PR.PRPROCEDURECODE AND CP.OFFICE_ID = PR.OFFICE_ID)
LEFT OUTER JOIN I_VSTAFFCHGBILL STFBILL
ON (CP.OFFICE_ID = STFBILL.OFFICE_ID AND CP.DNCP_PRACTICEID = STFBILL.V69_STPRACTICEID AND
CP.DNCP_STAFFID_CREDITED = STFBILL.V69_STSTAFFID)
LEFT OUTER JOIN I_VPROVCHGBILL BILL
ON (STFBILL.V69_STSTAFFID = BILL.V58_PROVPROVIDERID AND STFBILL.OFFICE_ID = BILL.OFFICE_ID)
LEFT OUTER JOIN I_DNCHGCOVERAGE CHGCV
ON (CP.OFFICE_ID = CHGCV.OFFICE_ID AND CP.DNCP_CHARGESID = CHGCV.TPSCHARGESID)
LEFT OUTER JOIN I_VDNPLANCHGCV1 CV
ON (CHGCV.CHGCVR1PLANID = CV.V9_DNPLANID AND CHGCV.OFFICE_ID = CV.OFFICE_ID)
LEFT OUTER JOIN I_VDEPTCHG DEP
ON (CP.DNCP_DEPARTMENTCODE = DEP.V4_DPTDEPARTMENTCODE AND CP.OFFICE_ID = DEP.OFFICE_ID)
LEFT OUTER JOIN I_VDNADDRSPAT ADDR
ON (CP.OFFICE_ID = ADDR.OFFICE_ID AND CP.DNCP_PATIENTID = ADDR.V8_TPSPERSONID)
LEFT OUTER JOIN I_VPERSONPAT PAT
ON (CP.OFFICE_ID = PAT.OFFICE_ID AND CP.DNCP_PATIENTID = PAT.V51_PRSNPERSONID)
LEFT OUTER JOIN IACCOUNT ACC
ON (CP.OFFICE_ID = ACC.OFFICE_ID AND CP.DNCP_ACCOUNTSID = ACC.ACCACCOUNTSID)
LEFT OUTER JOIN IPAYMENT PMT
ON (CP.OFFICE_ID = PMT.OFFICE_ID AND CP.DNCP_PAYMENTSID = PMT.PMTPAYMENTSID)
LEFT OUTER JOIN I_VLUPROCCLASS CL
ON (PR.OFFICE_ID = CL.OFFICE_ID AND PR.PRCLASS = CL.V85_LUCLOOKUPCODE)
LEFT OUTER JOIN I_VSVCCNTRPROC SVC
ON (CP.OFFICE_ID = SVC.OFFICE_ID AND CP.DNCP_SERVICECENTERID = SVC.V75_SVCSERVICECENTERID)
LEFT OUTER JOIN I_DNDXPROCEVENT DX
ON (CP.OFFICE_ID = DX.OFFICE_ID AND CP.DNCP_PROCEDUREEVENTSID = DX.TPSPROCEDUREEVENTSID)
INNER JOIN (
SELECT CP.DNCP_PATIENTID AS PSNNMBR,
MAX(CP.DNCP_FROMSERVICEDATE) AS SVCDT,
MAX(CP.DNCP_AMOUNT_CHARGE) AS CHGAMT
FROM I_DNCHGSPMTS CP
LEFT OUTER JOIN IPROCEDURE PR
ON (CP.DNCP_PROCEDURECODE = PR.PRPROCEDURECODE AND CP.OFFICE_ID = PR.OFFICE_ID)
LEFT OUTER JOIN I_VSTAFFCHGBILL STFBILL
ON (CP.OFFICE_ID = STFBILL.OFFICE_ID AND CP.DNCP_PRACTICEID = STFBILL.V69_STPRACTICEID AND
CP.DNCP_STAFFID_CREDITED = STFBILL.V69_STSTAFFID)
LEFT OUTER JOIN I_VPROVCHGBILL BILL
ON (STFBILL.V69_STSTAFFID = BILL.V58_PROVPROVIDERID AND STFBILL.OFFICE_ID = BILL.OFFICE_ID)
LEFT OUTER JOIN I_DNCHGCOVERAGE CHGCV
ON (CP.OFFICE_ID = CHGCV.OFFICE_ID AND CP.DNCP_CHARGESID = CHGCV.TPSCHARGESID)
LEFT OUTER JOIN I_VDNPLANCHGCV1 CV
ON (CHGCV.CHGCVR1PLANID = CV.V9_DNPLANID AND CHGCV.OFFICE_ID = CV.OFFICE_ID)
LEFT OUTER JOIN I_VDEPTCHG DEP
ON (CP.DNCP_DEPARTMENTCODE = DEP.V4_DPTDEPARTMENTCODE AND CP.OFFICE_ID = DEP.OFFICE_ID)
LEFT OUTER JOIN I_VDNADDRSPAT ADDR
ON (CP.OFFICE_ID = ADDR.OFFICE_ID AND CP.DNCP_PATIENTID = ADDR.V8_TPSPERSONID)
LEFT OUTER JOIN I_VPERSONPAT PAT
ON (CP.OFFICE_ID = PAT.OFFICE_ID AND CP.DNCP_PATIENTID = PAT.V51_PRSNPERSONID)
LEFT OUTER JOIN IACCOUNT ACC
ON (CP.OFFICE_ID = ACC.OFFICE_ID AND CP.DNCP_ACCOUNTSID = ACC.ACCACCOUNTSID)
LEFT OUTER JOIN IPAYMENT PMT
ON (CP.OFFICE_ID = PMT.OFFICE_ID AND CP.DNCP_PAYMENTSID = PMT.PMTPAYMENTSID)
LEFT OUTER JOIN I_VLUPROCCLASS CL
ON (PR.OFFICE_ID = CL.OFFICE_ID AND PR.PRCLASS = CL.V85_LUCLOOKUPCODE)
LEFT OUTER JOIN I_VSVCCNTRPROC SVC
ON (CP.OFFICE_ID = SVC.OFFICE_ID AND CP.DNCP_SERVICECENTERID = SVC.V75_SVCSERVICECENTERID)
LEFT OUTER JOIN I_DNDXPROCEVENT DX
ON (CP.OFFICE_ID = DX.OFFICE_ID AND CP.DNCP_PROCEDUREEVENTSID = DX.TPSPROCEDUREEVENTSID)
WHERE CP.OFFICE_ID = 'EDQV'
AND CP.DNCP_ACTIVITYTYPE = 'CHG'
--AND (CP.DNCP_PROCEDURECODE LIKE '99490' OR CP.DNCP_PROCEDURECODE LIKE 'CCM')
AND PAT.V51_PRSNSEX = 'F'
AND (DATEDIFF(DAY,PAT.V51_PRSNDOB,GETDATE())/365) >= 65
AND SUBSTRING(DEP.V4_DPTNAME,1,5) IN ('PRIMA','WOMEN','URGIC')
AND (DATEDIFF(DAY,CP.DNCP_POSTDATE,GETDATE())) < 720
--FOLLOWING PROVIDES NON DIABETICS
AND (DX.PROCEVENTDIAG1CODE NOT IN ('E11.65', 'E11.9', 'E10.9', 'E13.8', 'E10.39', 'E11.00', 'E11.69', 'E11.319', 'E11.42', 'E11.359', 'E11.329', 'E13.9', 'E10.8', 'E11.59', 'E11.39', 'H35.00') OR
DX.PROCEVENTDIAG2CODE NOT IN ('E11.65', 'E11.9', 'E10.9', 'E13.8', 'E10.39', 'E11.00', 'E11.69', 'E11.319', 'E11.42', 'E11.359', 'E11.329', 'E13.9', 'E10.8', 'E11.59', 'E11.39', 'H35.00') OR
DX.PROCEVENTDIAG3CODE NOT IN ('E11.65', 'E11.9', 'E10.9', 'E13.8', 'E10.39', 'E11.00', 'E11.69', 'E11.319', 'E11.42', 'E11.359', 'E11.329', 'E13.9', 'E10.8', 'E11.59', 'E11.39', 'H35.00') OR
DX.PROCEVENTDIAG4CODE NOT IN ('E11.65', 'E11.9', 'E10.9', 'E13.8', 'E10.39', 'E11.00', 'E11.69', 'E11.319', 'E11.42', 'E11.359', 'E11.329', 'E13.9', 'E10.8', 'E11.59', 'E11.39', 'H35.00'))
GROUP BY CP.DNCP_PATIENTID
) AS MAXSVCDATE
ON (CP.DNCP_PATIENTID = MAXSVCDATE.PSNNMBR AND CP.DNCP_FROMSERVICEDATE = MAXSVCDATE.SVCDT AND CP.DNCP_AMOUNT_CHARGE = MAXSVCDATE.CHGAMT)
WHERE CP.OFFICE_ID = 'EDQV'
AND PAT.V51_PRSNPERSONNUMBER NOT IN (
SELECT PAT.V51_PRSNPERSONNUMBER
FROM I_DNCHGSPMTS CP
LEFT OUTER JOIN IPROCEDURE PR
ON (CP.DNCP_PROCEDURECODE = PR.PRPROCEDURECODE AND CP.OFFICE_ID = PR.OFFICE_ID)
LEFT OUTER JOIN I_VSTAFFCHGBILL STFBILL
ON (CP.OFFICE_ID = STFBILL.OFFICE_ID AND CP.DNCP_PRACTICEID = STFBILL.V69_STPRACTICEID AND
CP.DNCP_STAFFID_CREDITED = STFBILL.V69_STSTAFFID)
LEFT OUTER JOIN I_VPROVCHGBILL BILL
ON (STFBILL.V69_STSTAFFID = BILL.V58_PROVPROVIDERID AND STFBILL.OFFICE_ID = BILL.OFFICE_ID)
LEFT OUTER JOIN I_DNCHGCOVERAGE CHGCV
ON (CP.OFFICE_ID = CHGCV.OFFICE_ID AND CP.DNCP_CHARGESID = CHGCV.TPSCHARGESID)
LEFT OUTER JOIN I_VDNPLANCHGCV1 CV
ON (CHGCV.CHGCVR1PLANID = CV.V9_DNPLANID AND CHGCV.OFFICE_ID = CV.OFFICE_ID)
LEFT OUTER JOIN I_VDEPTCHG DEP
ON (CP.DNCP_DEPARTMENTCODE = DEP.V4_DPTDEPARTMENTCODE AND CP.OFFICE_ID = DEP.OFFICE_ID)
LEFT OUTER JOIN I_VDNADDRSPAT ADDR
ON (CP.OFFICE_ID = ADDR.OFFICE_ID AND CP.DNCP_PATIENTID = ADDR.V8_TPSPERSONID)
LEFT OUTER JOIN I_VPERSONPAT PAT
ON (CP.OFFICE_ID = PAT.OFFICE_ID AND CP.DNCP_PATIENTID = PAT.V51_PRSNPERSONID)
LEFT OUTER JOIN IACCOUNT ACC
ON (CP.OFFICE_ID = ACC.OFFICE_ID AND CP.DNCP_ACCOUNTSID = ACC.ACCACCOUNTSID)
LEFT OUTER JOIN IPAYMENT PMT
ON (CP.OFFICE_ID = PMT.OFFICE_ID AND CP.DNCP_PAYMENTSID = PMT.PMTPAYMENTSID)
LEFT OUTER JOIN I_VLUPROCCLASS CL
ON (PR.OFFICE_ID = CL.OFFICE_ID AND PR.PRCLASS = CL.V85_LUCLOOKUPCODE)
LEFT OUTER JOIN I_VSVCCNTRPROC SVC
ON (CP.OFFICE_ID = SVC.OFFICE_ID AND CP.DNCP_SERVICECENTERID = SVC.V75_SVCSERVICECENTERID)
LEFT OUTER JOIN I_DNDXPROCEVENT DX
ON (CP.OFFICE_ID = DX.OFFICE_ID AND CP.DNCP_PROCEDUREEVENTSID = DX.TPSPROCEDUREEVENTSID)
WHERE CP.OFFICE_ID = 'EDQV'
AND CP.DNCP_ACTIVITYTYPE = 'CHG'
AND BILL.V58_PROVSORTNAME LIKE '%LOCASCIO%'
AND (DATEDIFF(DAY,CP.DNCP_POSTDATE,GETDATE())) < 720
)如果我运行该语句并排除主WHERE CP.OFFICE_ID = 'EDQV'之后的所有内容,它会运行得很好。如果我包含所有内容并将其更改为IN语句,而不是NOT IN语句,它将运行得很好。我运行了独立的查询,并将它们拉到Excel中手动执行,这样我就知道我应该返回多少条记录。
如果我一起排除NOT IN语句,我会得到3,315条记录。如果我把它变成一条IN语句,我得到的是283,它是准确的,并且在Excel中得到了验证。因此,如果我将其更改为NOT IN语句,我应该会得到3,032条记录。然而,我得到的却是零!我是不是错过了什么愚蠢的东西?
如果有帮助,此代码的要点是返回所有65岁以上的非糖尿病女性患者的联系人列表,这些患者在过去两年中曾在初级保健、妇女护理或紧急护理中就诊,而Locascio医生在过去两年中也没有见过她们。还需要它为每个人只返回一行。
发布于 2016-12-08 02:47:08
我的猜测是,在这种情况下,PAT.V51_PRSNPERSONNUMBER可以为null,最有可能在NOT IN的左侧,但也可能在子选择中。在谓词中,表达式NULL = NULL的值为UNKNOWN (与TRUE或FALSE相反)。这样做的效果是从结果中排除任何行。由于(NULL != NULL)也具有未知值,因此无论哪种方式,都会排除相同的行。NOT IN谓词也是如此。如果上面的列是NULL,它将不会与带有in或NOT IN运算符的subselect中返回的NULL值匹配。(如果你以前没有经历过,这通常是不直观的)
至于为什么PAT.V51_PRSNPERSONNUMBER为null,它可能处于联接条件中,用于联接IVERSIONPAT视图的PAT别名实例,或者表返回不匹配--对于左联接,意味着所有PAT列都将为NULL。第一步是单独运行子选择,看看上面的列是否真的为某些行返回了NULL --可能是完整的3032个缺少的行,然后从那里开始。
https://stackoverflow.com/questions/41024353
复制相似问题