select
p.patientid,
p.serprovcode,
p.dob,
p.gencode,
g.latitude,
g.longitude,
g.domicilecode,
g.quintile,
e1.unlref as ethnicity1,
e2.unlref as ethnicity2,
e3.unlref as ethnicity3,
c1.readcode as alc_readcode,
c1.whenclass as alc_readcode_date,
c2.whenclass as brief_advice_date,
m.whenmeasure as advanced_form_date,
m.value1 as exceeds_guideline,
m.value2 as at_risk,
m.value4 as where_referred,
m.value5 as full_audit_done,
m.value7 as is_pregnant,
m.value8 as units_per_session,
m.value9 as how_often,
m.value10 as binge_drinking
from patient p
left join DOB2YEARS(p.dob) ON 1=1
left join geocode g on g.anybodyid = p.patientid
left join ethnicity e1 on e1.ethcode = p.ethcode
left join ethnicity e2 on e2.ethcode = p.ethcode2
left join ethnicity e3 on e3.ethcode = p.ethcode3
left join classification c1 on
c1.patientid = p.patientid
and c1.rowinactive = 0
and c1.readcode in ('136K.00', '136L.00', '136M.00')
and c1.whenclass = (
select max(whenclass) from classification c1a
where c1a.patientid = c1.patientid
and c1a.rowinactive = 0
and c1a.readcode in ('136K.00', '136L.00', '136M.00')
)
left join classification c2 on
c2.patientid = p.patientid
and c2.rowinactive = 0
and c2.readcode = '6792.00'
and c2.whenclass = (
select max(whenclass) from classification c2a
where c2a.patientid = c2.patientid
and c2a.rowinactive = 0
and c2a.readcode = '6792.00'
)
left join measurement m on
m.patientid = p.patientid
and m.rowinactive = 0
and m.scncode = 'ALCR'
and m.whenmeasure = (
select max(whenmeasure) from measurement ma
where ma.patientid = m.patientid
and ma.rowinactive = 0
and ma.scncode = 'ALCR'
)
where
p.regcode = 'R'
and p.enrolmentcode = 'C'
and DOB2YEARS.YEARS >= '15'