Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 722
  • Last Modified:

SQL Query to detect Dups

Hi all,

I have several tables that need joined and duplicate records detected. (NOT DELETED). So here is the Layout;

Table Contacts has a fields I need to match on;
Field 1 = Last_Name
Field 2 = First_Name
Field 3 = Date_Of_Birth
Also in this table needs joined to Table Patients with these 2 fields are = and they are Contact_ID from the Contacts table joined to Patients Table Field = Patient_ID. These are the same in both tables.
Now from the Patients Table, I need 1 Field called End_Date. THIS is the PRIMARY field that this Query needs to revolve around with the above data.

So here is the key. If the End_Date is NULL that means that the record is ACTIVE in the Database, if it is NOT NULL then that record is already marked inactive. MEANING I dont care if I have duplicates with 1 record marked Inactive and the other record is Active.

ALSO, last piece to the Puzzle, I need another JOIN from Contacts Table, field will be again a 1 to 1 where the field Contact_ID in the Contacts Table = Policies Table field is also Contact_ID.

Thank you very much, Im new to SQL and this one is driving me crazy
0
dmanisit
Asked:
dmanisit
  • 5
  • 4
1 Solution
 
Jesus RodriguezIT ManagerCommented:
Can you post the table Structure??
0
 
Jesus RodriguezIT ManagerCommented:
Will Be something like this more or less

SELECT C.LAST_NAME
      ,C.FIRST_NAME
      ,C.DATE_OF_BITRTH
      ,P.END_DATE
      ,O.POLICIES_DESC
FROM CONTACTS as C
 INNER JOIN PATIENS as P
 ON P.PATIENTID=C.PATIENT_ID
   LEFT JOIN POLICIES AS O
   ON O.CONTACT_ID=C.CONTACT_ID
WHERE P.END_DATE IS NOT NULL

THe fields that you want from each table can be extracted on the select with the letter that is assign toi the table and the field for example

From Policies - Description of the policies
  O.POLICIES_DESCRIPT
0
 
dmanisitAuthor Commented:
Here are the table structures
Patients.JPG
contacts.JPG
policies.JPG
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
dmanisitAuthor Commented:
So as of thus far, to get your script to work, this is the changes I have made

SELECT C.Last_Name ,C.First_Name ,C.Date_of_Birth, P.End_Date
From pm.Contacts as C
INNER JOIN pm.Patients as P
ON P.Patient_ID = C.Contact_ID
 LEFT JOIN pm.Policies AS O
 ON O.Contact_ID = C.Contact_ID
WHERE P.END_DATE IS NOT NULL
0
 
Jesus RodriguezIT ManagerCommented:
Yeap, that's the correct one
0
 
dmanisitAuthor Commented:
So the script is pulling ALL Patients that are inactive due to the last line of code.

WHERE P.END_DATE IS NOT NULL

But heres what I need;

I need to only FIND the Patients that have multiple records   but thats conditional for example if there are 2 john smiths and 1 of those records Have an End_Date, then I DONT want to display this guy at all. I ONLY want when there are 2 Mary Janes and End_Date = NULL to display those
0
 
dmanisitAuthor Commented:
What do you think about a Count(*) column somehow??????
0
 
Jesus RodriguezIT ManagerCommented:
You can move the condition to the top like this

SELECT C.Last_Name
             ,C.First_Name
             ,C.Date_of_Birth
             ,P.End_Date
From pm.Contacts as C
INNER JOIN
    (SELECT PATIENS_ID,End_Date
     FROM pm.Patients
     WHERE END_DATE IS NOT NULL) as P
ON P.Patient_ID = C.Contact_ID
 LEFT JOIN pm.Policies AS O
 ON O.Contact_ID = C.Contact_ID
0
 
lomo74Commented:
check this
;with cte as (
	select	C.Contact_ID, C.First_Name, C.Last_Name, C.Date_Of_Birth, /* C.other_fields... , */
			O.Group_Name, /* O.other_fields... , */
			P.End_Date
	from	pm.Contacts C
	inner	join pm.Patients P
	on		P.Patient_ID = C.Contact_ID
	inner	join pm.Policies O
	on		O.Contact_ID = C.Contact_ID
	where	P.End_Date is null
)
select	*
from	cte T1
where	exists (
			-- check for duplicates i.e. a record with
			-- different ID and same name and birth date
			select	*
			from	cte T2
			where	T2.First_Name = T1.First_Name
			and		T2.Last_Name = T1.Last_Name
			and		T2.Date_Of_Birth = T1.Date_Of_Birth
			and		T2.Contact_ID != T1.Contact_ID
		)

Open in new window

0
 
dmanisitAuthor Commented:
thank you very much
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now