468,248 Members | 1,528 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,248 developers. It's quick & easy.

Identify Dulicates/Compare data in table to itself

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
Jul 3 '07 #1
3 1382
Motoma
3,237 Expert 2GB
How do you determine if a person is unique? That is, how do you define duplication?
Jul 3 '07 #2
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...
Jul 3 '07 #3
Motoma
3,237 Expert 2GB
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.
Jul 3 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Kiran B. | last post: by
1 post views Thread by =?Utf-8?B?SGF3aw==?= | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.