Data Integrity: Gender & Prefix

One script that I ran on a regular basis had to do with a individual's gender and prefix. In general, they should agree and when they don't, something is wrong.

select master_customer_id, label_name, gender_code, name_prefix, formal_salutation, nickname
from customer
where customer_status_code='ACTIVE'
and ((gender_code='M' and name_prefix in ('Ms.','Mrs.','Miss'))
or (gender_code='F' and name_prefix in ('Mr.')))

If I had them all agreeing and then one day there were some that didn't agree it would mean that either someone had just created the record and entered one wrong or someone had changed the name of the customer (instead of creating a new customer record) and changed one but not the other.

Some further updates I did with these fields would be populating null values, for example if the prefix was Mr. then the gender is M.

update customer set gender_code='F'
where record_type='I'
and gender_code is null
and customer_status_code='ACTIVE'
and name_prefix in ('Ms.','Miss','Mrs.')

update customer set gender_code='M'
where record_type='I'
and gender_code is null
and customer_status_code='ACTIVE'
and name_prefix in ('Mr.')

You could also do the opposite, updating the prefix (and label name, search name, formal salutation, etc.) when the gender is known but the prefix is missing.

Applies to: TIMSS5, TIMSS6, & Personify