/* patient details */
select
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
r.name AS 'GP',
rs.display_name AS 'practice',
pd.address,
pd.post_code
from outreach.patient_dispatch_detail pdd
join hilis4.patients p on pdd.patient_id = p.id
join hilis4.patient_demographics pd on pd.patient_id = p.id
join hilis4.referral_sources rs on pd.practice_id = rs.id
join hilis4.referrers r on pd.gp_id = r.id
where p.nhs_number = ?
/* all requests & pack dispatches */
select
r.id,
r.request_number,
r.year,
fo.`option` as 'outcome',
rpd.pack_due,
rpd.pack_sent,
rpd.return_due
from hilis4.requests r
join hilis4.patient_case pc on r.patient_case_id = pc.id
join hilis4.patients p on pc.patient_id = p.id
join outreach.request_pack_dispatch rpd on rpd.request_id = r.id
join outreach.request_followup rfu on rfu.request_id = r.id
join followup_options fo on rfu.followup_option_id = fo.id
where p.nhs_number = ?
order by r.created_at
/* results */
select
r.id,
lp.field_label,
rr.result
from outreach.request_results rr
join outreach.lab_params lp on rr.param_id = lp.id
join hilis4.requests r on rr.request_id = r.id
join hilis4.patient_case pc on r.patient_case_id = pc.id
join hilis4.patients p on pc.patient_id = p.id
where p.nhs_number = ?
order by r.id