Click here to Skip to main content
15,883,758 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to create the group_id for all the records in the table in sql server. group_id should be created based on:
 1. if ssn is matched select all matched ssn in one group like ssn - 1234 has 10 rec so group it as g_1234 or ssn-8789 has 10 rec g_8789
 2. if name is matched select all matched name in one group as g_name
 3. if name+ dob is matched then selected all match case in one group as g_name_dob
 4. if two or more records have different ssn but name is matched with any grouped rec then it will go to the g_name group as name is matched
 5. likely if two or more records have different ssn but name and dob both matched with any grouped record then it will go the same g_name_dob group
 6. in case ssn is null or empty, but name is matched with any of the existing grouped record, then that group_id will be updated for this record.

please let me know how best I can achieve it. 


What I have tried:

using while loop. not think best approach to do
Posted
Updated 18-Nov-17 22:49pm
Comments
CHill60 19-Nov-17 6:29am    
I've just noticed that you have chosen an email address as your display name. This is not a good idea on an open site as you can be targeted for spam

1 solution

Use GROUP BY[^]
CASE[^] might also be useful.
Don't use a loop, the solution is set - based, not procedural
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900