I have a database in SQL2008 of customers, There is an ID number for the customer. What im trying to do is to create a NEWID field.
We have duplicated customers that have different ID fields, I am combining the Surname and Postcode to find the duplicated customers.
As you can see in the image with an example, i need to do the following.
1.Check if PostSur field have a match,
2.Check if there is more than 1 IDNO,
3.Check the next IDNO against the First IDNO and if there is a unique entry in the Name field. Then populate that record with the First IDNO in a new field called NewCode.
4.Otherwise the Next IDNO populate the NewCode with 'Dupe'
5.Populate all other IDNO in the NewCode also.
So i can then hide the Dupes and i should then have unique customers using NewCode.
I have the following SQL which uses
ROW_NUMBER but cant figure out how to do the above with it?
select * from ( select *, row_number() over (partition by PostSur order by Postsur) as rowID from Aron_Reporting.dbo.Customer_NewSort ) as rowID order by PostSUr
any help would be great