[hmrn_patient_treatment_data]
SELECT
pt.id,
l.location,
tt.description as 'tx_type',
td.description as 'tx_detail',
pt.start_date,
pt.end_date,
ro.description as 'response'
FROM hmrn.patient_treatment pt
JOIN hmrn.locations l on pt.location_id = l.id
LEFT JOIN hmrn.treatment_details td on pt.tx_detail_id = td.id
LEFT JOIN hmrn.response_options ro on pt.response_id = ro.id
JOIN hmrn.treatment_types tt on pt.tx_type_id = tt.id
WHERE patient_id = ?
ORDER BY start_date, end_date
[hmrn_patient_referral_pathway]
SELECT
pr.id,
pr.referral_date,
rto.description as 'referral_type',
so1.description as 'referred_from',
so2.description as 'referred_to',
pr.date_first_seen
FROM hmrn.patient_referrals pr
JOIN hmrn.referral_type_options rto on pr.referral_type_id = rto.id
JOIN hmrn.referral_source_options so1 on pr.from_source_id = so1.id
JOIN hmrn.referral_source_options so2 on pr.to_source_id = so2.id
WHERE pr.patient_id = ?
ORDER BY pr.referral_date
[hmrn_all_patient_params]
SELECT
c.category,
p.param_name,
pp.result
FROM hmrn.patient_params pp
JOIN hmrn.parameters p on pp.param_id = p.id
JOIN hmrn.category_parameter cp on cp.parameter_id = p.id
JOIN hmrn.categories c on cp.category_id = c.id
WHERE pp.patient_id = ?
[hmrn_category_patient_params]
SELECT
p.param_name,
pp.result
FROM hmrn.patient_params pp
JOIN hmrn.parameters p on pp.param_id = p.id
JOIN hmrn.category_parameter cp on cp.parameter_id = p.id
JOIN hmrn.categories c on cp.category_id = c.id
WHERE pp.patient_id = ?
and c.category = ?
[hmrn_parameter_constraints]
SELECT
p.param_name,
p.field_type,
d.not_done,
d.not_stated
FROM hmrn.parameters p
LEFT JOIN hmrn.defaults_and_ranges d on d.param_id = p.id
[hmrn_total_request_counts]
SELECT
pc.patient_id,
COUNT(pc.patient_id)
FROM requests r
JOIN patient_case pc on (r.patient_case_id = pc.id)
WHERE pc.patient_id IN (??)
GROUP BY pc.patient_id
[hmrn_defaults_and_ranges]
SELECT d.*
FROM hmrn.defaults_and_ranges d
JOIN hmrn.parameters p on d.param_id = p.id
WHERE param_name = ?
[hmrn_category_params]
SELECT
p.param_name,
p.id
FROM hmrn.parameters p
JOIN hmrn.category_parameter cp on cp.parameter_id = p.id
JOIN hmrn.categories c on cp.category_id = c.id
WHERE c.category = ?
[hmrn_menu_options]
SELECT
p.param_name,
m.options
FROM hmrn.parameters p
JOIN hmrn.menu_options m on m.param_id = p.id
[hmrn_patient_staging]
/* using dbix->select now */
[hmrn_section_data]
SELECT p.id
FROM hmrn.parameters p
JOIN hmrn.category_parameter cp on cp.parameter_id = p.id
JOIN hmrn.categories c on cp.category_id = c.id
WHERE c.category = ?
[hmrn_imaging_options]
SELECT id, option_type
FROM hmrn.imaging_options
[hmrn_patient_imaging]
SELECT scan_type, dataset, imaging_option_id
FROM hmrn.patient_imaging_option io
JOIN hmrn.patient_imaging_event ie on io.imaging_event_id = ie.id
WHERE ie.patient_id = ?
[hmrn_patient_diagnostic_categories]
SELECT
DISTINCT(t7.description)
FROM
requests t1
JOIN patient_case t2 ON (t1.patient_case_id = t2.id)
JOIN patients t3 ON (t2.patient_id = t3.id)
JOIN request_report_view t4 ON (t1.id = t4.request_id)
JOIN diagnoses t5 ON (t4.diagnosis_id = t5.id)
JOIN icdo_category t6 ON (t5.icdo3 = t6.icdo3)
JOIN diagnostic_categories t7 ON (t6.diagnostic_category_id = t7.id)
WHERE
t2.patient_id = ?
[hmrn_calculate_patient_age]
SELECT p.dob, pe.date as 'diagnosis'
FROM patients p
JOIN ( hmrn.patient_event pe JOIN hmrn.events e on pe.event_id = e.id
and e.description = 'diagnosis' )
on pe.patient_id = p.id
WHERE pe.patient_id = ?
[hmrn_staging_sites]
SELECT description,location
FROM hmrn.staging_sites
[hmrn_non_unique_params]
SELECT param_name
FROM hmrn.category_parameter cp
JOIN hmrn.parameters p on cp.parameter_id = p.id
GROUP BY parameter_id
HAVING COUNT(*) > 1
[outreach_lab_params]
SELECT t.description, p.param_name, p.field_label, p.field_type, d.*
FROM outreach.lab_params p
JOIN outreach.result_types t on (p.department_id = t.id)
LEFT JOIN outreach.defaults_and_ranges d on (d.param_id = p.id)
[outreach_lab_results]
SELECT t.description, p.param_name, r.result
FROM outreach.request_results r
JOIN outreach.lab_params p on (r.param_id = p.id)
JOIN outreach.result_types t on (p.department_id = t.id)
WHERE r.request_id = ?
[outreach_requested_lab_tests]
SELECT t2.test_name
FROM request_lab_test_status t1
JOIN lab_tests t2 on t1.lab_test_id = t2.id
JOIN lab_test_status_options t3 on t1.status_option_id = t3.id
WHERE t1.request_id = ?
and t3.description = 'complete'
[outreach_patient_demographics]
SELECT
pd.*,
r.name as 'practitioner',
a.address as 'alternate_address',
a.post_code 'alternate_post_code',
ppd.dispatch_to,
rs.display_name,
bt.tube_type
FROM patient_demographics pd
JOIN referral_sources rs on (pd.practice_id = rs.id)
JOIN referrers r on (pd.gp_id = r.id)
LEFT JOIN outreach.patient_dispatch_detail ppd on (ppd.patient_id = pd.patient_id)
LEFT JOIN outreach.practice_blood_tube bt on (bt.practice_id = rs.id)
LEFT JOIN outreach.patient_alternate_address a on (a.patient_id = ppd.patient_id)
WHERE pd.patient_id = ?
[outreach_patient_questionnaire]
/* discontinued */
[outreach_questionnaire_details]
/* discontinued */
[outreach_patient_notes]
SELECT detail
FROM outreach.patient_notes
WHERE patient_id = ?
[outreach_menu_options]
SELECT field_name, detail
FROM outreach.menu_options
[outreach_followup_data]
SELECT
rf.followup_option_id,
cr.appointment_date,
pd.pack_due,
pd.pack_sent,
pd.return_due
FROM outreach.request_followup rf
LEFT JOIN outreach.request_pack_dispatch pd on (pd.request_id = rf.request_id)
LEFT JOIN outreach.request_clinic_return cr on (cr.request_id = rf.request_id)
WHERE rf.request_id = ?
[outreach_overdue_packs]
SELECT pd.request_id, p.nhs_number, pd.pack_sent
FROM outreach.request_pack_dispatch pd
JOIN outreach.request_followup fo on (pd.request_id = fo.request_id)
JOIN outreach.followup_options o on (fo.followup_option_id = o.id)
JOIN requests r on (pd.request_id = r.id)
JOIN patient_case pc on (r.patient_case_id = pc.id)
JOIN patients p on (pc.patient_id = p.id)
JOIN patient_demographics d on d.patient_id = p.id
WHERE pd.return_due <= CURRENT_DATE()
/* BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AND CURRENT_DATE() */
and o.option LIKE '%month'
and d.status <> 'dead'
ORDER BY pd.return_due, r.year, r.request_number
[outreach_overdue_packs_two]
/* replaced by outreach_most_recent */
[outreach_most_recent]
SELECT p.nhs_number, MAX(DATE(r.created_at)) as 'most_recent'
FROM requests r
JOIN ( patient_case pc JOIN patients p on (pc.patient_id = p.id) )
on (r.patient_case_id = pc.id)
JOIN ( request_initial_screen ris JOIN screens s on (ris.screen_id = s.id) )
on (ris.request_id = r.id)
WHERE s.description IN (??)
/* AND r.created_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) */
GROUP BY p.nhs_number
[outreach_packs_due_details]
SELECT
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
p.gender,
pc.unit_number,
d2.name as 'diagnosis',
d2.icdo3,
IF (aa.patient_id IS NOT NULL, aa.address, d.address) as 'address',
IF (aa.patient_id IS NOT NULL, aa.post_code, d.post_code) as 'post_code',
d.contact_number,
r2.name as 'referrer',
rs1.display_name as 'location',
IF (r3.national_code = 'G9999998', 'UNKNOWN', r3.name) as 'practitioner',
rs2.display_name as 'practice_address',
rr2.`result` as 'electrophoresis',
rr3.`result` as 'neoplastic_b_cells',
bt.tube_type
FROM requests r1
JOIN patient_case pc on (r1.patient_case_id = pc.id)
JOIN patients p on (pc.patient_id = p.id)
JOIN patient_demographics d on (d.patient_id = p.id)
JOIN referrer_department rd on (r1.referrer_department_id = rd.id)
JOIN referrers r2 on (rd.referrer_id = r2.id)
JOIN referrers r3 on (d.gp_id = r3.id)
JOIN referral_sources rs1 on (pc.referral_source_id = rs1.id)
JOIN referral_sources rs2 on (d.practice_id = rs2.id)
JOIN request_report_view rr on (rr.request_id = r1.id)
JOIN diagnoses d2 on (rr.diagnosis_id = d2.id)
JOIN outreach.request_pack_dispatch pd on (pd.request_id = r1.id)
JOIN outreach.request_followup fu on (pd.request_id = fu.request_id)
JOIN outreach.followup_options o on (fu.followup_option_id = o.id)
LEFT JOIN ( outreach.request_results rr2
JOIN outreach.lab_params lp on ( rr2.param_id = lp.id
and lp.param_name = 'electrophoresis' ) )
on rr2.request_id = r1.id
LEFT JOIN ( outreach.request_results rr3
JOIN outreach.lab_params lp2 on ( rr3.param_id = lp2.id
and lp2.param_name = 'neoplastic_b_cells' ) )
on rr3.request_id = r1.id
LEFT JOIN outreach.patient_alternate_address aa on (aa.patient_id = p.id)
LEFT JOIN outreach.practice_blood_tube bt on (bt.practice_id = rs2.id)
WHERE pd.pack_due <= CURRENT_DATE()
and pd.pack_sent IS NULL
and o.option LIKE '%month'
and d.status <> 'dead'
and d.practice_id NOT IN (
SELECT practice_id FROM outreach.non_participant_practice
)
[outreach_packs_due_summary]
SELECT
r.id,
r.request_number,
r.year,
r.created_at,
p.last_name,
p.first_name,
p.nhs_number,
rs.display_name as 'location',
pd.pack_due
FROM requests r
JOIN patient_case pc on (r.patient_case_id = pc.id)
JOIN patients p on (pc.patient_id = p.id)
JOIN referral_sources rs on (pc.referral_source_id = rs.id)
JOIN patient_demographics d on (d.patient_id = p.id)
JOIN outreach.request_pack_dispatch pd on (pd.request_id = r.id)
JOIN outreach.request_followup fo on (pd.request_id = fo.request_id)
JOIN outreach.followup_options o on (fo.followup_option_id = o.id)
WHERE pd.pack_due <= CURRENT_DATE()
and pd.pack_sent IS NULL
and o.option LIKE '%month'
and d.status <> 'dead'
and d.practice_id NOT IN (
SELECT practice_id FROM outreach.non_participant_practice
)
ORDER BY pd.pack_due, r.year, r.request_number
[outreach_practices]
SELECT
rs.id,
rs.display_name,
rs.organisation_code,
bt.tube_type
FROM referral_sources rs
LEFT JOIN outreach.practice_blood_tube bt on (bt.practice_id = rs.id)
WHERE id in (
SELECT DISTINCT(practice_id)
FROM outreach.patient_dispatch_detail dd
JOIN patient_demographics pd on (dd.patient_id = pd.patient_id)
WHERE organisation_code <> 'V81999'
)
ORDER BY bt.tube_type DESC, rs.display_name
[outreach_pack_labels]
SELECT
p.last_name,
p.first_name,
CASE
WHEN dd.dispatch_to = 'GP' THEN rs.display_name /* includes post_code */
WHEN dd.dispatch_to = 'alternate' THEN aa.address
ELSE d.address
END as 'address',
CASE
WHEN dd.dispatch_to = 'alternate' THEN aa.post_code
WHEN dd.dispatch_to = 'home' THEN d.post_code
END as 'post_code'
FROM requests r
JOIN patient_case pc on (r.patient_case_id = pc.id)
JOIN patients p on (pc.patient_id = p.id)
JOIN patient_demographics d on (d.patient_id = p.id)
JOIN referral_sources rs on (d.practice_id = rs.id)
JOIN outreach.request_pack_dispatch pd on (pd.request_id = r.id)
JOIN outreach.request_followup fo on (pd.request_id = fo.request_id)
JOIN outreach.followup_options o on (fo.followup_option_id = o.id)
JOIN outreach.patient_dispatch_detail dd on (dd.patient_id = p.id)
LEFT JOIN outreach.patient_alternate_address aa on (aa.patient_id = p.id)
WHERE pd.pack_due <= CURRENT_DATE()
and pd.pack_sent IS NULL
and o.option LIKE '%month'
and d.status <> 'dead'
and d.practice_id NOT IN (
SELECT practice_id FROM outreach.non_participant_practice
)
ORDER BY p.last_name, p.first_name
[outreach_authorised_between_dates]
SELECT
r.id,
r.request_number,
r.year,
r.created_at,
rh.time as 'authorised',
p.id as 'patient_id',
p.last_name,
p.first_name,
rs.display_name as 'location'
FROM requests r
JOIN patient_case pc on (r.patient_case_id = pc.id)
JOIN patients p on (pc.patient_id = p.id)
JOIN referral_sources rs on (pc.referral_source_id = rs.id)
JOIN patient_demographics d on (d.patient_id = p.id)
JOIN request_history rh on (rh.request_id = r.id)
JOIN request_initial_screen ris on (ris.request_id = r.id)
JOIN screens s on (ris.screen_id = s.id)
WHERE s.description IN ('outreach', 'community monitoring')
and d.status <> 'dead'
and rh.action = 'authorised'
and rh.time between ? and ?
ORDER BY r.year, r.request_number
[outreach_report_labels]
SELECT
p.last_name,
p.first_name,
d.address,
d.post_code,
rs.display_name
FROM patients p
JOIN patient_demographics d on (d.patient_id = p.id)
JOIN referral_sources rs on (d.practice_id = rs.id)
WHERE p.id IN (??)
ORDER BY p.last_name, p.first_name
[outreach_request_results]
SELECT
t3.param_name,
t3.field_label,
if (t2.result = t4.default, NULL, t2.result) as 'result',
t1.created_at
FROM requests t1
JOIN outreach.request_results t2 on (t2.request_id = t1.id)
JOIN outreach.lab_params t3 on (t2.param_id = t3.id)
LEFT JOIN outreach.defaults_and_ranges t4 on (t4.param_id = t3.id)
WHERE t1.id in (
SELECT r.id
FROM requests r
JOIN patient_case pc on (r.patient_case_id = pc.id)
WHERE pc.patient_id = ?
)
AND t3.param_name in (??)
ORDER BY t1.created_at
[outreach_params_for_department]
SELECT p.param_name, p.id
FROM outreach.lab_params p
JOIN outreach.result_types t on p.department_id = t.id
WHERE t.description = ?
[outreach_clinic_return_alert]
SELECT
r.request_number,
r.year,
p.last_name,
p.first_name,
p.nhs_number,
p.dob,
pc.unit_number,
DATE(rfu.time) as 'date',
d.name as 'diagnosis',
rs.display_name as 'location',
GROUP_CONCAT(ec.contact_address) as 'contacts'
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN patients p on pc.patient_id = p.id
JOIN referral_sources rs on pc.referral_source_id = rs.id
JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
on rr.request_id = r.id
JOIN email_contacts ec on ( ec.referral_source_id = rs.id
and ec.`type` = 'mdt' )
JOIN ( outreach.request_followup rfu JOIN outreach.followup_options o
on rfu.followup_option_id = o.id ) on rfu.request_id = r.id
WHERE o.`option` = 'clinic_return'
and DATE(rfu.time) >= ?
GROUP BY r.id
[gallium_trial_referred_to_kiel]
/* discontinued - see _local.sql */
[gallium_trial_request_specimen]
/* discontinued - see _local.sql */
[gallium_trial_data]
/* discontinued - see _local.sql */
[gallium_data]
SELECT
r.id as 'request_id',
CONCAT(r.request_number, '/', r.year - 2000) as 'hmds_ref',
pc.unit_number as 'accession_number',
er.external_reference,
rs.display_name as 'source',
d.name as 'diagnosis',
d.icdo3,
rr.clinical_details,
rr.comment,
rr.specimen_quality,
DATE(r.created_at) as 'registered',
DATE(rh.time) as 'authorised',
MAX(CASE WHEN ls2.section_name = 'Immunohistochemistry' THEN rrs.results_summary END)
AS 'immunohistochemistry_summary',
MAX(CASE WHEN ls2.section_name = 'Molecular' THEN rrs.results_summary END)
AS 'molecular_summary',
MAX(CASE WHEN lt.test_name = 'bcl2' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'bcl2',
MAX(CASE WHEN lt.test_name = 'bcl6' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'bcl6',
MAX(CASE WHEN lt.test_name = 'cd3' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cd3',
MAX(CASE WHEN lt.test_name = 'cd5' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cd5',
MAX(CASE WHEN lt.test_name = 'cd10' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cd10',
MAX(CASE WHEN lt.test_name = 'cd20' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cd20',
MAX(CASE WHEN lt.test_name = 'cd23' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cd23',
MAX(CASE WHEN lt.test_name = 'cd79' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cd79',
MAX(CASE WHEN lt.test_name = 'bcl1' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'cyclin_d1',
MAX(CASE WHEN lt.test_name = 'foxp1' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'foxp1',
MAX(CASE WHEN lt.test_name = 'irf4' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'irf4',
MAX(CASE WHEN lt.test_name = 'ki67' AND ls.section_name = 'Immunohistochemistry'
THEN ltr.result END) AS 'ki67',
MAX(CASE WHEN lt.test_name = 'bcl2' AND ls.section_name = 'FISH'
THEN ltr.result END) AS 'BCL2',
MAX(CASE WHEN lt.test_name = 'bcl6' AND ls.section_name = 'FISH'
THEN ltr.result END) AS 'BCL6',
MAX(CASE WHEN lt.test_name = 'igh' AND ls.section_name = 'FISH'
THEN ltr.result END) AS 'IgH'
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN referral_sources rs on pc.referral_source_id = rs.id
JOIN ( request_trial rt JOIN clinical_trials ct on rt.trial_id = ct.id )
on rt.request_id = r.id
JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
on rr.request_id = r.id
LEFT JOIN request_external_ref er on er.request_id = r.id
LEFT JOIN ( request_lab_test_results ltr
JOIN lab_tests lt on ltr.lab_test_id = lt.id
JOIN lab_sections ls on lt.lab_section_id = ls.id )
on ltr.request_id = r.id
LEFT JOIN ( request_result_summaries rrs JOIN lab_sections ls2 on
rrs.lab_section_id = ls2.id )
on rrs.request_id = r.id
WHERE ct.trial_name = 'Roche Gallium'
and rs.display_name LIKE 'Quintiles%' /* exclude adjudication cases from Kiel */
and ( d.icdo3 IS NOT NULL or d.name IN (??) )
GROUP by r.id
ORDER BY r.id
[gallium_pending]
SELECT
r.id,
pc.unit_number as 'qlabs_ref',
CONCAT(r.request_number, '/', r.year - 2000) as 'hmds_ref',
d.name as 'diagnosis',
CONCAT_WS(" ", rr.morphology, rr.comment) as 'morphology_comment'
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN patients p on pc.patient_id = p.id
JOIN referral_sources rs on pc.referral_source_id = rs.id
JOIN ( request_trial rt JOIN clinical_trials ct on rt.trial_id = ct.id )
on rt.request_id = r.id
JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
JOIN ( request_report_view rr JOIN diagnoses d on rr.diagnosis_id = d.id )
on rr.request_id = r.id
WHERE ct.trial_name = 'Roche Gallium'
and rs.display_name RLIKE 'Quintiles'
and d.icdo3 IS NULL
and d.name NOT IN (??)
GROUP BY r.id /* in case duplicates in request_history */
ORDER BY d.name, r.id
[genomics_requests]
SELECT DISTINCT(r.id)
FROM requests r
LEFT JOIN request_lab_test_results t2 on t2.request_id = r.id
LEFT JOIN request_history t3 on ( t3.request_id = r.id
and t3.`action` = 'screened' )
LEFT JOIN request_lab_test_history t4 on ( t4.request_id = r.id
and t4.action LIKE 'uploaded % file' )
WHERE /* timestamp on any table that should trigger data feed */
r.created_at >= DATE_SUB(NOW(), INTERVAL ? SECOND) or
r.updated_at >= DATE_SUB(NOW(), INTERVAL ? SECOND) or
t2.time >= DATE_SUB(NOW(), INTERVAL ? SECOND) or
t3.time >= DATE_SUB(NOW(), INTERVAL ? SECOND) or
t4.time >= DATE_SUB(NOW(), INTERVAL ? SECOND)
[genomics_unit_number]
SELECT pc.id, pc.unit_number
FROM patient_case pc
JOIN requests r on r.patient_case_id = pc.id
JOIN request_history rh on ( rh.request_id = r.id and rh.`action` = 'screened' )
WHERE r.id = ?
and rh.time >= DATE_SUB(NOW(), INTERVAL ? SECOND)
[genomics_demographics]
SELECT
r.id as 'request_id',
r.request_number,
r.year,
r.created_at as 'registered',
p.last_name,
p.first_name,
p.nhs_number,
p.gender,
p.dob,
rs.display_name as 'referral_source',
rs.organisation_code,
IF( s.description rlike 'cancer', 'Cancer', s.description ) as 'arm',
r2.name as 'referrer_name',
r2.national_code as 'referrer_code',
rh.time as 'screened'
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN patients p on pc.patient_id = p.id
JOIN referral_sources rs on pc.referral_source_id = rs.id
JOIN referrer_department rd on r.referrer_department_id = rd.id
JOIN referrers r2 on rd.referrer_id = r2.id
LEFT JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
on ris.request_id = r.id
LEFT JOIN request_history rh on ( rh.request_id = r.id
and rh.`action` = 'screened' )
WHERE r.id IN (??)
[genomics_results]
SELECT lt.test_name, tr.result
FROM request_lab_test_results tr
JOIN lab_tests lt on tr.lab_test_id = lt.id
JOIN lab_sections ls on lt.lab_section_id = ls.id
WHERE tr.request_id = ?
and ls.section_name = ?
[genomics_storage]
SELECT
rs.vialId,
rs.sample,
rs.part_number,
rs.source,
sr.plateId as 'rack_id',
rs.vial_location
FROM request_storage rs
LEFT JOIN storage_racks sr on rs.rack_id = sr.id
WHERE rs.request_id = ?
[genomics_consent]
SELECT u.first_name, u.last_name
FROM request_lab_test_history t1
JOIN users u on t1.user_id = u.id
WHERE t1.request_id = ?
and t1.action = 'auto-set Consent given status to complete'
[genomics_sample_linkage_form]
/* participant ID allocated, family_id exists and NOT sample_taken result */
SELECT r.id, r.request_number, r.year, p.last_name, p.first_name, p.nhs_number
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN patients p on pc.patient_id = p.id
JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
on ris.request_id = r.id
JOIN ( request_lab_test_results tr1 JOIN lab_tests lt1
on tr1.lab_test_id = lt1.id and lt1.test_name = 'family_id')
on tr1.request_id = r.id
LEFT JOIN ( request_lab_test_results tr2 JOIN lab_tests lt2
on tr2.lab_test_id = lt2.id and lt2.test_name = 'sample_taken')
on tr2.request_id = r.id
WHERE s.description = 'rare disease'
and pc.unit_number <> 'UNKNOWN'
and tr2.lab_test_id IS NULL
[genomics_sample_linkage_form_data]
SELECT
p.last_name,
p.first_name,
p.dob,
p.nhs_number,
p.gender,
pc.unit_number as 'participant_id',
s.description as 'disease_type',
rs.display_name as 'location',
ref.name as 'referrer',
MAX(CASE WHEN lt.test_name = 'family_id' THEN tr.result END) AS 'family_id',
MAX(CASE WHEN lt.test_name = 'total_samples' THEN tr.result END) AS 'total_samples'
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN patients p on pc.patient_id = p.id
JOIN referral_sources rs on pc.referral_source_id = rs.id
JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
on ris.request_id = r.id
JOIN ( referrer_department rd JOIN referrers ref on rd.referrer_id = ref.id )
on r.referrer_department_id = rd.id
LEFT JOIN ( request_lab_test_results tr JOIN lab_tests lt on tr.lab_test_id = lt.id )
on tr.request_id = r.id
WHERE r.id IN (??)
GROUP BY r.id
