/*This snippet will find a patients's gender if it is recorded.
The logic behind this code is to present any formal recording of gender with an option
to assume the patient sex is the gender if gender is not formally recorded. The decision
to include the sex as gender will need to be made by understanding the context of the
reporting, including any formal specifications provided by the reporting body. */
select p.pat_id
/*This case statement uses the gender recorded in biographics and if this is not known
then uses the latest gender recorded as a qualifier and if this is not known
then uses the patient's sex (remove is not desired) and if this is not known
then returns 'Not recorded'*/
, cast(replace(case
when trim(gl.display_name) <> '' then gl.display_name
when trim(cu_stripfirstword(g.gender, '#')) <> '' then cu_stripfirstword(g.gender, '#')
--remove this next line if you don't want it to assume that the patient's sex is there gender if it is not specified:
when trim(x.sex_description) <> '' then x.sex_description
else 'Not recorded'
end, 'Cisgender', 'Cisgender - ' || x.sex_description) as varchar(100)) as gender
from patient p
/*This join looks up the sex at birth*/
left outer join sex x on x.sex_code = p.pat_sex
/*This join looks up the gender if recorded on the biographics form*/
left outer join general_lookup gl on gl.lookup_type = 'Genders'
and gl.lookup_id = p.gender_id
/*This join looks up the latest gender recorded as a qualifier*/
left outer join (--g
select pm.pat_id
, max(cu_formatdatetime(pm.pat_morb_act_date, 'YYYYMMDD') || lpad(pm.morb_no,8,'0') || '#' ||
mrt.measure_ref_type_desc) gender
from measurement_type mt
join pat_measure me on me.measure_type_no = mt.measure_type_no
and mt.measure_type_desc_uc = 'GENDER'
join measurement_ref_type mrt on mrt.measure_ref_type_no = me.measure_ref_type_no
join pat_morb_view pm on pm.morb_no = me.morb_no
group by 1
) g on g.pat_id = p.pat_id
