1

First, here's my code:

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
        )

If I run the statement and exclude everything after the main WHERE CP.OFFICE_ID = 'EDQV', it runs great. If I include everything and change it to an IN statement rather than a NOT IN statement, it runs great. I've run independent queries and pulled them into Excel to do it manually so I know what numbers of records I should be returning.

If I exclude the NOT IN statement all together, I get 3,315 records. If I make it an IN statement, I get 283, which is accurate and verified in Excel. Therefore, if I change it to a NOT IN statement, I should get 3,032 records. However, I get zero! Am I missing something stupid here?

If it helps, the point of this code is to return a contact list for all female patients over the age of 65 who are not diabetic and have been seen in primary care, womens' care, or urgent care in the last two years, who also have NOT been seen by Dr. Locascio in the last two years. Need it to only return one line per person as well.

halfer
  • 19,824
  • 17
  • 99
  • 186
RSQ2
  • 31
  • 4
  • 2
    Probably a duplicate of http://stackoverflow.com/q/5231712/73226 – Martin Smith Dec 07 '16 at 18:12
  • @MartinSmith Genius! I apologize...I promise I searched beforehand but did not come across that post. Thank you very much. Problem solved! And I learned something new! haha – RSQ2 Dec 07 '16 at 18:16

1 Answers1

0

My guess is that this is a case where PAT.V51_PRSNPERSONNUMBER can be null, most likely on the left of the NOT IN, but perhaps in the subselect too. In a predicate the expression NULL = NULL has the value UNKNOWN (as opposed to TRUE or FALSE). This has the effect of excluding any rows from the result. Since (NULL != NULL) also has the UNKNOWN value, the same rows are excluded either way. The same is true for the NOT IN predicate. If the above column is NULL, it won't match a NULL value returned in the subselect either with the IN or with the NOT IN operator. (This is typically not intuitive if you haven't experienced it before)

As to why PAT.V51_PRSNPERSONNUMBER is null, it may be in the join condition used to join in the PAT aliased instance of IVERSIONPAT view or table is returning no match -- for the left join it measn all PAT columns will be NULL. The first step is to run the subselect on its own and see if the above column is in fact returning NULL for some rows-- likely the full 3032 missing rows and go from there.

John Horman
  • 121
  • 4