Mark Minasi's Tech Forum
Register Calendar Latest Topics Chat
 
 
 


Reply
  Author   Comment  
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 212
Reply with quote  #1 

select FirstName, Lastname, FullName, MemberID from Contactbase

Group by FirstName, Lastname, FullName

having count(*) >1

 

 

This worked well for pulling duplicates out of the Contactbase table in the CRM database, but I wanted to include the pair of records for comparison.

 

Any Ideas?


__________________
Curt Spanburgh
0
cj_berlin

Avatar / Picture

Senior Member
Registered:
Posts: 226
Reply with quote  #2 
a nested query would be an inefficient but simple way to accomplish this, I think...
__________________
Evgenij Smirnov

My personal blog (German): http://www.it-pro-berlin.de/
My stuff on PSGallery: https://www.powershellgallery.com/profiles/it-pro-berlin.de/
0
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 212
Reply with quote  #3 
Thank You.   I've found another snippet that I adopted that returns all fields.

select * from Contactbase a join (select firstname, Lastname from contactbase group by firstname, Lastname having count
(*) >1 ) b on a.firstname = b.FirstName and a.lastname = b.lastname

This way I can use this to create the dataset in the Report  and choose fields.  It returns the pairs of duplicates rather than just one of the two.

Now the trick is to get the report to run within CRM 2016 On premise.   Hosted systems are sometimes schitzo.

__________________
Curt Spanburgh
0
cspanburgh

Avatar / Picture

Senior Member
Registered:
Posts: 212
Reply with quote  #4 
Well, in the end , here was the query that worked.

Select * from ContactBase AS a INNER JOIN

(Select Fullname,Firstname,LastName,Altai_Memberid

From ContactBase

Group By Firstname, Lastname, FullName, Altai_Memberid

Having (Count(*) . 1)) AS b ON a.firstname = b.Firstname AND a.LastName = b.Lastname

AND A.FullName = b.FullName and a.Memberid = b.Memberid.

If there are any CRM devs reading this, I wrote another query that worked with an On Premise CRM 2016 instance, but the client I'm doing this for has a hosted CRM 2013 instance on VMs.   The one I wrote under the 2016 instance did not work on the 2013 instance and thus the changes.

I checked for differences in the table's (Contactbase) indexes and the 2013 instance has only one additional index.  Did not see any field differences in the 1/2 hour I checked so I did not come up with a reason why the earlier query returned the result that you expect from Select * from Contactbase.

But as you can imagine from the query that worked , it returned all fields available in the table.  Perfect for using the query to create a dataset for the report.

And Yes!!!   Your right.  The client wants additional changes to the report.

Well......  as long as it's billable!!!!




__________________
Curt Spanburgh
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:

Easily create a Forum Website with Website Toolbox.