Identify Dulicates/Compare data in table to itself

[angelnjj]

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_NAM E,
ORGANIZ.ORG_ID,
ACPERSON.FULL_N AME,
ACPERSON.PERSON _ID,
ACPERSON.PERSON _GID,
ACPERSON.PID,
COALESCE(ACORGP ERUS.ORGROLTYTX ,'NULL') AS TITLE,
COALESCE(ACPERS ON.SALUTATION,' NULL') AS SALUTATION,
COALESCE(ACADDR ESS.ADD_ADD1,'N ULL') AS ADDRESS1,
COALESCE(ACADDR ESS.ADD_ADD2,'N ULL') AS ADDRESS2,
COALESCE(ACADDR ESS.ADD_ADD3,'N ULL') AS ADDRESS3,
COALESCE(ACADDR ESS.ADD_CITY,'N ULL') AS CITY,
COALESCE(ACADDR ESS.ADD_STATE,' NULL') AS STATE,
COALESCE(ACADDR ESS.ADD_CODE,'N ULL') 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.C REATE_DAT,1) AS CREATEDATE,
CNT_CREAT.PER_I NIT,
ACORGPERUS.PERS ON_ID,
ACORGPERUS.PERS ON_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_NU MBER) <=5 THEN 0 ELSE 1 END)END) AS CKPHONE,

/*(CASE WHEN ACPERSON.FULL_N AME IN
(SELECT PERSON.FULL_NAM E
FROM PERSON
INNER JOIN ORGPERUS ON PERSON.PERSON_I D = ORGPERUS.PERSON _ID
INNER JOIN ORGANIZ ON ORGPERUS.ORG_ID = ORGANIZ.ORG_ID
WHERE
PERSON.PER_INIT IS NULL AND
PERSON.CREATE_D AT NOT BETWEEN '5/1/07 AND '5/31/07')
THEN 0 ELSE 1 END) AS CKDUPE,
*/

(CASE WHEN ACPERSON.FULL_N AME NOT LIKE '%. %' THEN 0 ELSE 1 END) AS CKFULLNAME,
(CASE WHEN COALESCE(ACORGP ERUS.ORGROLTYTX ,'NULL') = 'NULL' THEN 0 ELSE 1 END) AS CKTITLE,
(CASE WHEN COALESCE(ACPERS ON.SALUTATION,' NULL') NOT LIKE '%. %' THEN 0 ELSE 1 END) AS CKSALUTATION,
(CASE WHEN (COALESCE(ACPER SON.SALUTATION, 'NULL') = 'NULL' or COALESCE(ACPERS ON.SALUTATION,' NULL') NOT LIKE '%. %') THEN 0 ELSE 1 END) AS CKSALUTATION,
(CASE WHEN COALESCE(ACADDR ESS.ADD_ADD1,'N ULL') = 'NULL' THEN 0 ELSE 1 END) AS CKADDRESS,
(CASE WHEN COALESCE(ACADDR ESS.ADD_CITY,'N ULL') = 'NULL' THEN 0 ELSE 1 END) AS CKCITY,
(CASE WHEN COALESCE(ACADDR ESS.ADD_CODE,'N ULL') = '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.PERS ON_ID = ACADDRESS.PERSO N_ID and ACORGPERUS.PERS ON_GID = ACADDRESS.PERSO N_GID
left outer join PERSON CNT_CREAT on ACORGPERUS.CREA TEPER = CNT_CREAT.PERSO N_ID and ACORGPERUS.CREA TEPERG = CNT_CREAT.PERSO N_GID
left outer join PHONE COFFR on ACORGPERUS.PERS ON_ID = COFFR.PERSON_ID and ACORGPERUS.PERS ON_GID = COFFR.PERSON_GI D and 2 = COFFR.PHSYN_ID
left outer join PHONE COFFD on ACORGPERUS.PERS ON_ID = COFFD.PERSON_ID and ACORGPERUS.PERS ON_GID = COFFD.PERSON_GI D and 7 = COFFD.PHSYN_ID
left outer join PHONE CMAIL on ACORGPERUS.PERS ON_ID = CMAIL.PERSON_ID and ACORGPERUS.PERS ON_GID = CMAIL.PERSON_GI D and 8 = CMAIL.PHSYN_ID
left outer join PERSON ACPERSON on ACORGPERUS.PERS ON_ID = ACPERSON.PERSON _ID and ACORGPERUS.PERS ON_GID = ACPERSON.PERSON _GID and ACORGPERUS.ORG_ ID > 0
left outer join PERSTYPUSE CRTUSE on ACORGPERUS.PERS ON_ID = CRTUSE.PERSON_I D and ACORGPERUS.PERS ON_GID = CRTUSE.PERSON_G ID
left outer join PERSTYP RT on CRTUSE.PERSTYP_ ID = RT.PERSTYP_ID and CRTUSE.PERSTYPG ID = 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_I D >= 1 AND ORGANIZ.ACCOUNT _TYPE = 1 AND ORGANIZ.CLIENT_ IN = 1 AND ORGANIZ.PROP_IN = 0 )) AND
((ACPERSON.CREA TE_DAT BETWEEN '5/1/07' AND '5/31/07')) AND
ACPERSON.PERSON _GID <>679 AND
ACPERSON.PID = 5985

ORDER BY ACPERSON.FULL_N AME

[Motoma]

How do you determine if a person is unique? That is, how do you define duplication?

[angelnjj]

I think a dupe is where the contact's name exactly matches the name of the previous entry and are within the same account. The Person_ID will be different, so that's not a dupe to me...

Thank you so much...

[Motoma]

I think a dupe is where the contact's name exactly matches the name of the previous entry and are within the same account. The Person_ID will be different, so that's not a dupe to me...

Thank you so much...

You would likely be better off setting a constraint on those two rows.