[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 = ?
[pack_dispatches]
/* 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 outreach.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