/*This snippet allows you to find the parent/guardian of a child.
It looks for a MOTHER, FATHER or CARER recorded as either kin or emergency
contact and prioritises them in that order then displays the first found.*/
select p.full_name
, cu_substr(max(guard.name),
cu_strpos('#', max(guard.name)) + 1,
strlen(max(guard.name)) - cu_strpos('.',max(guard.name))) PARENT_GUARDIAN_NAME
, cu_substr(max(guard.name),
1,
cu_strpos('#' ,max(guard.name)) - 1) PARENT_GUARDIAN_RELATION
...
from patient p
left outer join (--guard
select pk.pat_id
, trim(pk.kin_type_desc_uc) ||
'#' ||
upper(pk.kin_name) name
from pat_kin_view pk
where pk.kin_type_desc_uc in ('MOTHER', 'FATHER', 'CARER')
union
select x.pat_id
, trim(kte.kin_type_desc_uc) ||
'#' ||
upper(x.emergency_contact_name) name
from patient x
join kin_type kte on kte.kin_type_no = x.emergency_contact_type
and kte.kin_type_desc_uc in ('MOTHER', 'FATHER', 'CARER')
) guard on guard.pat_id = p.pat_id
and p.age_birthyears_today < 18
where p.age_birthyears_today < 18
group by 1
...
/*This snippet will show a patient's chronic diseases.
These are conditions marked as summary and belonging to the CHRONIC
CONDITIONS (ALL) group.*/
select p.full_name
, list(distinct cd.nat_lan_term, ', ') CHRONIC_CONDITIONS
...
from patient p
left outer join (pat_morb_view cd
join morb_group_link mgl on mgl.morb_type_no = cd.morb_type_no
and cd.morb_subtype = 'C'
and cd.summary_item = 'T'
join morb_group mg on mg.group_no = mgl.group_no
and mg.group_desc_uc = 'CHRONIC CONDITIONS (ALL)') on cd.pat_id = p.pat_id
group by 1
...
/*This snippet will show a patient's current mailing address.*/
select p.full_name
, pa.address_line1
, pa.address_line2
, coalesce(la.locality_name || ' ' ||
la.locality_state || ' ' ||
la.locality_post_code, la.locality_name) locality
...
from patient p
left outer join (--addr
select px.pat_id
, max(cu_formatdatetime(px.from_date, 'YYYYMMDD') || '.' || lpad(px.pat_address_no, 8, '0')) mail
from pat_address px
where px.mail_indic = 'Y'
group by 1
) addr on addr.pat_id = p.pat_id
left outer join (pat_address pa
join locality la on la.locality_no = pa.locality_no) on pa.pat_address_no = cu_stripfirstword(coalesce(addr.mail, '00000000.00000000'), '.')
...
/*This snippet will show a patient's current temporary address.*/
select p.full_name
, pa.address_line1
, pa.address_line2
, coalesce(la.locality_name || ' ' ||
la.locality_state || ' ' ||
la.locality_post_code, la.locality_name) locality
...
from patient p
left outer join (--addr
select px.pat_id
, max(case
when px.temp_indic = 'Y' then
cu_formatdatetime(px.from_date, 'YYYYMMDD') || '.' || lpad(px.pat_address_no, 8, '0')
else null
end) temp
, max(case
when px.current_address = 'T' then
cu_formatdatetime(px.from_date, 'YYYYMMDD') || '.' || lpad(px.pat_address_no, 8, '0')
else null
end) home
from pat_address px
group by 1
) addr on addr.pat_id = p.pat_id
left outer join (pat_address pa
join locality la on la.locality_no = pa.locality_no) on pa.pat_address_no = cu_stripfirstword(coalesce(addr.temp, '00000000.00000000'), '.')
and cu_striplastword(addr.temp, '.') >= cu_striplastword(addr.home, '.')
...
