2
I'm going to do my best to describe purpose and what I need...here goes.
I'm writing a data entry "quality" report and working. I'm trying to identify anytime a user adds a new contact to the db during a date range that already existed within the account prior to the first date of the range. When the contact existed already, the returned value should be 1, if they didn't exist the returned value should be 0 (I'll then total up all the 1s to figure out a % of created contacts added correctly). The contact and account table are seperate, joined by an Orgperus "lookup" sort of table. It is OK if the newly created contact name appears twice in the db, but it's not OK if it appears in an account where that name already existed.
So far...I know there are 3 tables that I need to use/join to get the right info. Person, Orgperus, and Organiz
Person (P) table lists all the Contacts in the db.
The columns are: P.Full_Name, P.Person_ID and P.Per_INIT, P.CREATE_DAT. The P.Person_PID for each row is unique.
The Orgperus (OP) table is a sort of x-ref table and contains 2 columns of importance: OP.Org_ID, OP.Person_ID
The Organiz (O) table lists all the Accounts in the db
The columns are O.Org_ID, O.Org_Name. The O.Org_ID is unique for each account/row.
What I have now works except that it shows contacts as a dupe even if they aren't within the same account
Trying to add the ORG_ID as an additional part of the comparison criteria in the case piece is where I get into trouble and don't know how to address.
Here is the entire query I'll build the report from...what's commented out is the section that proving problematic.
SELECT
ORGANIZ.ORG_NAME,
ORGANIZ.ORG_ID,
ACPERSON.FULL_NAME,
ACPERSON.PERSON_ID,
ACPERSON.PERSON_GID,
ACPERSON.PID,
COALESCE(ACORGPERUS.ORGROLTYTX,'NULL') AS TITLE,
COALESCE(ACPERSON.SALUTATION,'NULL') AS SALUTATION,
COALESCE(ACADDRESS.ADD_ADD1,'NULL') AS ADDRESS1,
COALESCE(ACADDRESS.ADD_ADD2,'NULL') AS ADDRESS2,
COALESCE(ACADDRESS.ADD_ADD3,'NULL') AS ADDRESS3,
COALESCE(ACADDRESS.ADD_CITY,'NULL') AS CITY,
COALESCE(ACADDRESS.ADD_STATE,'NULL') AS STATE,
COALESCE(ACADDRESS.ADD_CODE,'NULL') AS POSTAL,
COALESCE(COFFR.PH_NUMBER, 'NULL') AS PHONEREC,
COALESCE(COFFD.PH_NUMBER,'NULL') AS PHONEDIR,
COALESCE(CMAIL.PH_NUMBER,'NULL') AS EMAIL,
CONVERT(VARCHAR(50),ACPERSON.CREATE_DAT,1) AS CREATEDATE,
CNT_CREAT.PER_INIT,
ACORGPERUS.PERSON_ID,
ACORGPERUS.PERSON_GID,
ACPERSON.retire,
(CASE WHEN COALESCE(CMAIL.PH_NUMBER,'NULL') = 'NULL' THEN 0 ELSE 1 END) AS CKEMAIL,
(CASE WHEN COALESCE(COFFR.PH_NUMBER,'NULL') = 'NULL' AND COALESCE(COFFD.PH_NUMBER,'NULL') = 'NULL' THEN 0 ELSE (CASE WHEN LEN(COFFR.PH_NUMBER) <=5 THEN 0 ELSE 1 END)END) AS CKPHONE,
/*(CASE WHEN ACPERSON.FULL_NAME IN
(SELECT PERSON.FULL_NAME
FROM PERSON
INNER JOIN ORGPERUS ON PERSON.PERSON_ID = ORGPERUS.PERSON_ID
INNER JOIN ORGANIZ ON ORGPERUS.ORG_ID = ORGANIZ.ORG_ID
WHERE
PERSON.PER_INIT IS NULL AND
PERSON.CREATE_DAT NOT BETWEEN '5/1/07 AND '5/31/07')
THEN 0 ELSE 1 END) AS CKDUPE,
*/
(CASE WHEN ACPERSON.FULL_NAME NOT LIKE '%. %' THEN 0 ELSE 1 END) AS CKFULLNAME,
(CASE WHEN COALESCE(ACORGPERUS.ORGROLTYTX,'NULL') = 'NULL' THEN 0 ELSE 1 END) AS CKTITLE,
(CASE WHEN COALESCE(ACPERSON.SALUTATION,'NULL') NOT LIKE '%. %' THEN 0 ELSE 1 END) AS CKSALUTATION,
(CASE WHEN (COALESCE(ACPERSON.SALUTATION,'NULL') = 'NULL' or COALESCE(ACPERSON.SALUTATION,'NULL') NOT LIKE '%. %') THEN 0 ELSE 1 END) AS CKSALUTATION,
(CASE WHEN COALESCE(ACADDRESS.ADD_ADD1,'NULL') = 'NULL' THEN 0 ELSE 1 END) AS CKADDRESS,
(CASE WHEN COALESCE(ACADDRESS.ADD_CITY,'NULL') = 'NULL' THEN 0 ELSE 1 END) AS CKCITY,
(CASE WHEN COALESCE(ACADDRESS.ADD_CODE,'NULL') = 'NULL' THEN 0 ELSE 1 END) AS CKZIP
from ORGANIZ
left outer join ORGPERUS ACORGPERUS on ORGANIZ.ORG_ID = ACORGPERUS.ORG_ID and ORGANIZ.ORG_GID = ACORGPERUS.ORG_GID
left outer join ADDRESS ACADDRESS on ACORGPERUS.PERSON_ID = ACADDRESS.PERSON_ID and ACORGPERUS.PERSON_GID = ACADDRESS.PERSON_GID
left outer join PERSON CNT_CREAT on ACORGPERUS.CREATEPER = CNT_CREAT.PERSON_ID and ACORGPERUS.CREATEPERG = CNT_CREAT.PERSON_GID
left outer join PHONE COFFR on ACORGPERUS.PERSON_ID = COFFR.PERSON_ID and ACORGPERUS.PERSON_GID = COFFR.PERSON_GID and 2 = COFFR.PHSYN_ID
left outer join PHONE COFFD on ACORGPERUS.PERSON_ID = COFFD.PERSON_ID and ACORGPERUS.PERSON_GID = COFFD.PERSON_GID and 7 = COFFD.PHSYN_ID
left outer join PHONE CMAIL on ACORGPERUS.PERSON_ID = CMAIL.PERSON_ID and ACORGPERUS.PERSON_GID = CMAIL.PERSON_GID and 8 = CMAIL.PHSYN_ID
left outer join PERSON ACPERSON on ACORGPERUS.PERSON_ID = ACPERSON.PERSON_ID and ACORGPERUS.PERSON_GID = ACPERSON.PERSON_GID and ACORGPERUS.ORG_ID > 0
left outer join PERSTYPUSE CRTUSE on ACORGPERUS.PERSON_ID = CRTUSE.PERSON_ID and ACORGPERUS.PERSON_GID = CRTUSE.PERSON_GID
left outer join PERSTYP RT on CRTUSE.PERSTYP_ID = RT.PERSTYP_ID and CRTUSE.PERSTYPGID = RT.PERSTYPGID
where
((ACPERSON.PER_INIT IS NULL)) and
((CNT_CREAT.PER_INIT IS NOT NULL OR CNT_CREAT.FULL_NAME IS NULL)) and
((ORGANIZ.ORG_ID >= 1 AND ORGANIZ.ACCOUNT_TYPE = 1 AND ORGANIZ.CLIENT_IN = 1 AND ORGANIZ.PROP_IN = 0 )) AND
((ACPERSON.CREATE_DAT BETWEEN '5/1/07' AND '5/31/07')) AND
ACPERSON.PERSON_GID <>679 AND
ACPERSON.PID = 5985
ORDER BY ACPERSON.FULL_NAME
