Как добавить group_id в SQL server

---

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. 

Что я уже пробовал:

использование цикла while. не думаю, что лучший подход, чтобы сделать

---