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'

