lundi 18 septembre 2017

MSSQL How to divide database records into even, random groups

tblNames

OrganizationID (int)
LastName (varchar)
...
GroupNumber (int)

GroupNumber is currently NULL for all records, I need an UPDATE statement to update this column.

I need to split up records on an OrganizationID level into even, random groups.

If there are < 20,000 records for an OrganizationID, I need 2 even, random groups. So records for that OrganizationID will have a GroupNumber of 1 or 2. There will be the same (or if odd number of records difference of only 1) number of records for GroupNumber 1 than for GroupNumber 2, and there will be no recognizable way to tell how a person got into a GroupNumber - i.e. LastNames that start with A-L are group 1, M-Z are group 2 would not be OK.

If there are > 20,000 records for an OrganizationID, I need 4 even, random groups. So records for that OrganizationID will have a GroupNumber of 1, 2, 3, or 4. There will be the same (or if odd number of records difference of only 1) number of records for each GroupNumber, and there will be no recognizable way to tell how a person got into a GroupNumber - i.e. LastNames that start with A-F are group 1, G-L are group 2, etc. would not be OK.

There are only about 20 organizations, so I can run an update statement 20 times, once per organizationID if needed.

I have full control of the table so I can add keys or columns, but for now this is what it is.

Would appreciate any help.




Aucun commentaire:

Enregistrer un commentaire