SQL Query find and replace duplicates with a "D" added to primary key

I wanted to know a way to find duplicates in a query and replace them with a case statement that adds a "D" or anything to note that there are 2 instances of the primary key.

Basically I want to show all records from table CTE, but I want it to replace any records that have 2 instances of the same name and birth date with a "D" in the contactID field. Where would I add a case when statement?

Looks like you are using T-SQL (MS SQL Server), which contains row_number function, allows you to number the duplicates. Partitioning tells row_number to restart the numbering at 1 when there is a grouping

Looks like you are using T-SQL (MS SQL Server), which contains row_number function, allows you to number the duplicates. Partitioning tells row_number to restart the numbering at 1 when there is a grouping change:

ok let me rephrase. I wrote a complex query and want to find the duplicates and mark them and add a "D" on the end in the ID field. Is there a different way than John_Vidmar's row number and partitioning within sql above? Something like a case statement? The system that I am sending the file to, would create 2 records of the same person with different ID's. One with the 'id' and one with the 'id' + 'D'

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Assuming that the Contact_ID is the primary key (and unique) and that you want to concatenate a 'D' at the end of it if there are matching names and birthdates in other records, the following should work:
with cte as
(select first_name fname, last_name lname, date_of_birth dob, count(*) cnt
from pm.contacts
group by first_name, last_name, date_of_birth)
select case when cte.cnt = 1 then C.Contact_ID
else c.Contact_ID||'D'
end as 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
inner join cte on
c.first_name = cte.fname and
c.last_name = cte.lname and
c.date_of_birth = cte.dob
where P.End_Date is null
The above is for Oracle but changing the concatenation operator from "||" to "+" should work for SQL Server.

0

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb. Luckily, there is a free version SQL Express, but does …