473,847 Members | 1,698 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Identify Dulicates/Compare data in table to itself

2 New Member
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
Jul 3 '07 #1
3 1705
Motoma
3,237 Recognized Expert Specialist
How do you determine if a person is unique? That is, how do you define duplication?
Jul 3 '07 #2
angelnjj
2 New Member
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 Recognized Expert Specialist
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

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

Similar topics

30
3533
by: Christian Seberino | last post by:
How does Ruby compare to Python?? How good is DESIGN of Ruby compared to Python? Python's design is godly. I'm wondering if Ruby's is godly too. I've heard it has solid OOP design but then I've also heard there are lots of weird ways to do some things kinda like Perl which is bad for me. Any other ideas?
1
3071
by: Brad H McCollum | last post by:
I'm writing an application using VB 6.0 as the front-end GUI, and the MSDE version of SQL Server as the back-end (it's a program for a really small # of users --- less then 3-4). I'm trying to determine, through the Visual Basic interface, the permissions of each user that's using the application on his/her machine. For example, let's say I'm user "Michael" that's sitting down at my machine using the app. I've written. The security...
5
10886
by: Megan | last post by:
Hi everybody- I'm helping a friend with a music database. She has an old one and is creating a new one. She wants to compare records and fields in the old database with records and fields in the new database. For instance, her old database has a table with Band Info in it. Her new database also has a table with Band Info in it but slightly different. I was wondering if there was an easy way to compare the fields from similar tables in...
9
6622
by: VMI | last post by:
I have two tables and I want to compare these two tables with a query( ie. "select * from A where B.key = A.key") and the result will be stored in a 3rd table (table C). is this possible? If necessary, I can create the schema for the 3rd table (C) since it'll be exactly like table A. But I'm more interested in being able to store the resulting set into another table. Thanks.
3
2301
by: Sam | last post by:
Hi Everyone, I have a stucture below stored in an arraylist and I want to check user's input (point x,y) to make sure there is no duplicate point x,y entered (string label can be duplicated). Is there a way to compare the new input point x,y without having to a loop and compare the whole object in the array list. I can't use the indexof method because it would compare the whole object. Public Structure
3
8908
by: Kiran B. | last post by:
Hi, I am new to .net. I have two Data Structure Type ... Sturcture A and Structure B. Structure A Public Fname as String Public LastName as String Public City as String Public Zip as String End Structure
17
4543
by: Mark A | last post by:
DB2 8.2 for Linux, FP 10 (also performs the same on DB2 8.2 for Windoes, FP 11). Using the SAMPLE database, tables EMP and EMLOYEE. In the followng stored procedure, 2 NULL columns (COMM) are selected into 2 different SP variables and compared for equal. They are both NULL, but do not compare as equal. When the Not NULL columns (SALARY) are compared, they do compare as equal.
11
35433
by: inpuarg | last post by:
I have 2 datatables. They are identical. I want to compare them by cell's content. They are all same. But dt1 == dt2 or dt1.GetHashCode() == dt2.GetHashCode() doesn 't work. There are big amount of rows in theese datatables . So i don 't want to enumerate each rows. This is not efficient and unacceptable for my current application.
1
1878
by: =?Utf-8?B?SGF3aw==?= | last post by:
Hello, I have been trying to get a duplicate field checker to work within my gridview using a custom validator. However with the code I have I am getting an error error: Object reference not set to an instance of an object here is my code:
0
9886
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
10647
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10706
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10338
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7056
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5911
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4528
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4119
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3164
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.