[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,
	/* pt.tx_cycles, */
    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_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_sub_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

[hmrn_parameter_menu_items]
/* New vertical menus for HRMN, replacing CSV menu items - James Doughty */
SELECT
    p.param_name, i.item_value
FROM hmrn.parameters p
    JOIN hmrn.parameter_menu_item m on m.param_id = p.id
    JOIN hmrn.menu_items i on m.item_id = i.item_id
ORDER BY param_name, item_value

[hmrn_param_categories]
/* returns categories that a parameter appears in
   Used "like" so that we can, if we wish, return all paramters with category.
*/
SELECT c.category
FROM hmrn.category_parameter cp
	JOIN hmrn.parameters p on cp.parameter_id = p.id
	JOIN hmrn.categories c on cp.category_id = c.id
WHERE p.param_name LIKE ?

[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 RLIKE 'week|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 RLIKE 'outreach|community monitoring'
	/* 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 RLIKE 'week|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 RLIKE 'week|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 RLIKE 'week|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_reports_to_issue]
SELECT
    r.id,
    r.request_number,
    r.year,
    p.last_name,
    p.first_name,
    p.dob,
    p.nhs_number,
    src.display_name as 'location',
    rh.`time` as 'auth_datetime'
FROM
   requests r
  JOIN request_history rh ON ( rh.request_id = r.id 
    AND rh.`action` = 'authorised' )
  JOIN ( patient_case pc JOIN patients p on pc.patient_id = p.id )
    on r.patient_case_id = pc.id
  JOIN referral_sources src on pc.referral_source_id = src.id
  JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
    on rs.request_id = r.id
  LEFT JOIN outreach.request_report_issued rri on rri.request_id = r.id
WHERE
  s.sample_code = 'CMP'
  and rri.request_id IS NULL
  and DATE(rh.`time`) > ? /* supply var or run as sub-query:
    ( SELECT MIN(DATE(`time`)) FROM outreach.request_report_issued ) */
GROUP BY r.id /* in case of duplicates in request_history  */
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 RLIKE '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]
/* replaced with SQLA::More method */
SELECT DISTINCT(r.id)
FROM requests r
    JOIN ( patient_case pc JOIN patients p on pc.patient_id = p.id )
        on r.patient_case_id = pc.id
    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
    p.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]
/* discontinued - merged with genomics_demographics */
SELECT pc.id
FROM patient_case pc
    JOIN requests r on r.patient_case_id = pc.id
	/* update anytime unit number = UNKNOWN in case script errors on 1st run post-screening
    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]
/* replaced with SQLA::More method */
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,
    pc.id as 'patient_case_id',
    pc.unit_number as 'participant_id',
	rs.display_name as 'referral_source',
    rs.organisation_code,
	CASE /* returns RD or Cancer (incl HaemOnc) or NULL: */
    	WHEN s.description = 'Rare disease' THEN s.description
    	WHEN sc.name IS NOT NULL THEN 'Cancer' END as 'arm',
    CASE s.description
        WHEN 'Rare disease' THEN NULL /* not required for Rare disease */
        ELSE s.description END as 'disease_type_registration',
	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 JOIN screen_category sc on s.category_id = sc.id )
	 		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 (??)
ORDER BY r.id

[genomics_data]
SELECT
	CONCAT_WS('.', u.first_name, u.last_name) as 'username',
    rs.display_name as 'source',
    CONCAT('G', r.request_number, '/', r.year - 2000) as 'lab_number',
    pc.unit_number as 'participant_id',
    s.description as 'presentation',
    DATE(rh.time) as 'registration_date',
    CASE WHEN s.description = 'Rare disease' THEN s.description ELSE sc.name END as 'arm',
    MAX(CASE WHEN lt.test_name = 'family_id' THEN tr.result END) AS 'family_id',
    MAX(CASE WHEN lt.test_name = 'approach_date' THEN tr.result END) AS 'approach_date',
    MAX(CASE WHEN lt.test_name = 'approach_followup_date' THEN tr.result END) AS 'followup_date',
    MAX(CASE WHEN lt.test_name = 'first_outcome' THEN tr.result END) AS 'first_outcome',
    MAX(CASE WHEN lt.test_name = 'second_outcome' THEN tr.result END) AS 'second_outcome',
    MAX(CASE WHEN lt.test_name = 'total_samples' THEN tr.result END) AS 'total_samples',
    MAX(CASE WHEN lt.test_name = 'consent_date' THEN tr.result END) AS 'consent_date',
    MAX(CASE WHEN lt.test_name = 'sample_taken' THEN tr.result END) AS 'sample_taken',
    MAX(CASE WHEN lt.test_name = 'paxgene_rna' THEN tr.result END) AS 'paxgene_rna',
    MAX(CASE WHEN lt.test_name = 'edta1_qc_type' THEN tr.result END) AS 'edta1_qc_type',
    MAX(CASE WHEN lt.test_name = 'edta1_qc_date' THEN tr.result END) AS 'edta1_qc_date',
    MAX(CASE WHEN lt.test_name = 'blood_summary_qc' THEN tr.result END) AS 'blood_summary_qc',
    MAX(CASE WHEN lt.test_name = 'qubit_blood' THEN tr.result END) AS 'qubit_blood',
    MAX(CASE WHEN lt.test_name = 'dna_lab_number' THEN tr.result END) AS 'dna_lab_number',
    MAX(CASE WHEN lt.test_name = 'clinic_sample_type' THEN tr.result END) AS 'clinic_sample_type',
    MAX(CASE WHEN lt.test_name = 'tumour_sample_taken' THEN tr.result END) AS 'tumour_sample_taken',
    MAX(CASE WHEN lt.test_name = 'unsent_sample_reason' THEN tr.result END) AS 'unsent_sample_reason',
    MAX(CASE WHEN lt.test_name = 'withdrawn_sample_reason' THEN tr.result END) AS 'withdrawn_sample_reason',
    MAX(CASE WHEN lt.test_name = 'tumour_type' THEN tr.result END) AS 'tumour_type',
    MAX(CASE WHEN lt.test_name = 'tumour_size' THEN tr.result END) AS 'tumour_size',
    MAX(CASE WHEN lt.test_name = 'tumour_lab_number' THEN tr.result END) AS 'tumour_lab_number',
    MAX(CASE WHEN lt.test_name = 'tumour_qc_type' THEN tr.result END) AS 'tumour_qc_type',
    MAX(CASE WHEN lt.test_name = 'tumour_qc_date' THEN tr.result END) AS 'tumour_qc_date',
    MAX(CASE WHEN lt.test_name = 'qubit_tumour' THEN tr.result END) AS 'qubit_tumour',
    MAX(CASE WHEN lt.test_name = 'tumour_summary_qc' THEN tr.result END) AS 'tumour_summary_qc',
    MAX(CASE WHEN lt.test_name = 'qubit_tumour_datetime' THEN tr.result END) AS 'qubit_tumour_datetime',
    MAX(CASE WHEN lt.test_name = 'saliva_sample_taken' THEN tr.result END) AS 'saliva_sample_taken',
    MAX(CASE WHEN lt.test_name = 'bm_sample_taken' THEN tr.result END) AS 'bm_sample_taken',
    MAX(CASE WHEN lt.test_name = 'bm_unsent_sample_reason' THEN tr.result END) AS 'bm_unsent_sample_reason',
    MAX(CASE WHEN lt.test_name = 'bm_sample_id' THEN tr.result END) AS 'bm_sample_id',
    MAX(CASE WHEN lt.test_name = 'bm_dna_lab_number' THEN tr.result END) AS 'bm_dna_lab_number',
    MAX(CASE WHEN lt.test_name = 'bm_dna_qubit' THEN tr.result END) AS 'bm_dna_qubit',
    MAX(CASE WHEN lt.test_name = 'bm_dna_qc_datetime' THEN tr.result END) AS 'bm_dna_qc_datetime',
    MAX(CASE WHEN lt.test_name = 'bm_dna_summary_qc' THEN tr.result END) AS 'bm_dna_summary_qc',
    MAX(CASE WHEN lt.test_name = 'saliva_dna_lab_number' THEN tr.result END) AS 'saliva_dna_lab_number',
    MAX(CASE WHEN lt.test_name = 'saliva_dna_summary_qc' THEN tr.result END) AS 'saliva_dna_summary_qc',
    MAX(CASE WHEN lt.test_name = 'saliva_dna_qubit' THEN tr.result END) AS 'saliva_dna_qubit',
    MAX(CASE WHEN lt.test_name = 'saliva_dna_qc_datetime' THEN tr.result END) AS 'saliva_dna_qc_datetime',
    MAX(CASE WHEN lt.test_name = 'dna_sent_ccp' THEN tr.result END) AS 'dna_sent_ccp',
    MAX(CASE WHEN lt.test_name = 'dna_arrived_ccp' THEN tr.result END) AS 'dna_arrived_ccp',
    MAX(CASE WHEN lt.test_name = 'dna_dispatched' THEN tr.result END) AS 'dna_dispatched',
    MAX(CASE WHEN lt.test_name = 'dna_consignment_number' THEN tr.result END) AS 'dna_consignment_number',
    MAX(CASE WHEN lt.test_name = 'omics_sent_ccp' THEN tr.result END) AS 'omics_sent_ccp',
    MAX(CASE WHEN lt.test_name = 'omics_arrived_ccp' THEN tr.result END) AS 'omics_arrived_ccp',
    MAX(CASE WHEN lt.test_name = 'omics_dispatched' THEN tr.result END) AS 'omics_dispatched',
    MAX(CASE WHEN lt.test_name = 'omics_consignment_number' THEN tr.result END) AS 'omics_consignment_number',
    MAX(CASE WHEN lt.test_name = 'withdrawal_date' THEN tr.result END) AS 'withdrawal_date',
    MAX(CASE WHEN lt.test_name = 'sent_from_histopathology' THEN tr.result END) AS 'sent_from_histopathology',
    MAX(CASE WHEN lt.test_name = 'received_by_cytogenetics' THEN tr.result END) AS 'received_by_cytogenetics',
    pn.detail as 'patient_notes',
    MAX(CASE WHEN lt.test_name = 'patient_deceased' THEN tr.result END) AS 'patient_deceased',
    MAX(CASE WHEN lt.test_name = 'blood_sending_lab' THEN tr.result END) AS 'blood_sending_lab',
    MAX(CASE WHEN lt.test_name = 'blood_sending_lab_date' THEN tr.result END) AS 'blood_sending_lab_date',
    MAX(CASE WHEN lt.test_name = 'blood_receiving_lab' THEN tr.result END) AS 'blood_receiving_lab',
    MAX(CASE WHEN lt.test_name = 'blood_receiving_lab_date' THEN tr.result END) AS 'blood_receiving_lab_date',
    MAX(CASE WHEN lt.test_name = 'tumour_sending_lab' THEN tr.result END) AS 'tumour_sending_lab',
    MAX(CASE WHEN lt.test_name = 'tumour_sending_lab_date' THEN tr.result END) AS 'tumour_sending_lab_date',
    MAX(CASE WHEN lt.test_name = 'tumour_receiving_lab' THEN tr.result END) AS 'tumour_receiving_lab',
    MAX(CASE WHEN lt.test_name = 'tumour_receiving_lab_date' THEN tr.result END) AS 'tumour_receiving_lab_date'
FROM requests r
    JOIN ( patient_case pc JOIN referral_sources rs on pc.referral_source_id = rs.id )
  		on r.patient_case_id = pc.id
    /* JOIN patients p on pc.patient_id = p.id // not collecting any patient info */
    JOIN ( request_history rh JOIN users u on rh.user_id = u.id )
        on ( rh.request_id = r.id and rh.action = 'registered' )
    /* left join request_lab_test_results & request_initial_screen to include unscreened req's */
    LEFT JOIN ( request_lab_test_results tr JOIN lab_tests lt on tr.lab_test_id = lt.id )
        on tr.request_id = r.id
    LEFT JOIN ( request_initial_screen ris JOIN ( screens s JOIN screen_category sc on s.category_id = sc.id )
        on ris.screen_id = s.id ) on ris.request_id = r.id
    LEFT JOIN patient_notes pn on pc.patient_id = pn.patient_id
GROUP BY r.id
/* LIMIT 10 */

[genomics_results]
/* replaced with SQLA::More method */
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]
/* replaced with SQLA::More method */
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]
/* replaced with SQLA::More method */
SELECT u.first_name, u.last_name,
    CASE
        WHEN  t1.`action` RLIKE 'Consent given' THEN 'consent_given'
        ELSE 'consent_withdrawn' END as 'action'
FROM request_lab_test_history t1
    JOIN users u on t1.user_id = u.id
WHERE t1.request_id = ?
    and t1.`action` RLIKE 'auto-set (Consent given|Withdrawal date) status to complete'

[genomics_slf_rare_disease]
SELECT /* participant ID allocated, family_id exists and NOT sample_taken result */
    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_lab_test_status ts JOIN lab_tests lt1 on ts.lab_test_id = lt1.id )
	 		on ts.request_id = r.id
	JOIN lab_test_status_options so on ts.status_option_id = so.id
    JOIN ( request_initial_screen ris JOIN screens s on ris.screen_id = s.id )
		on ris.request_id = r.id
/* // temporarily suspended
	JOIN ( request_lab_test_results tr1 JOIN lab_tests lt0
        on tr1.lab_test_id = lt0.id and lt0.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
    LEFT JOIN ( request_lab_test_results tr3 JOIN lab_tests lt3
        on tr3.lab_test_id = lt3.id and lt3.test_name = 'first_outcome')
            on tr3.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr4 JOIN lab_tests lt4
        on tr4.lab_test_id = lt4.id and lt4.test_name = 'second_outcome')
            on tr4.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr5 JOIN lab_tests lt5
        on tr5.lab_test_id = lt5.id and lt5.test_name = 'withdrawal_option')
            on tr5.request_id = r.id
WHERE s.description = 'Rare disease'
    and NOT pc.unit_number = 'UNKNOWN'
    and lt1.test_name = 'sample_taken' /* been requested */
    and so.description = 'new' /* sample_taken status not complete */
    and tr2.lab_test_id IS NULL /* no sample_taken result */
    and ( tr3.result IS NULL or tr3.result NOT IN ('Declined', 'No longer eligible') )
    and ( tr4.result IS NULL or tr4.result NOT IN ('Declined', 'No longer eligible') )
    and ( tr5.result IS NULL or NOT tr5.result = 'Full' )
ORDER BY r.id

[genomics_slf_cancer]
SELECT /* participant ID allocated and NOT ( sample_taken + tumour_sample_taken results ) */
    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 JOIN screen_category sc
        on s.category_id = sc.id ) on ris.screen_id = s.id )
            on ris.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr1 JOIN lab_tests lt1
        on tr1.lab_test_id = lt1.id and lt1.test_name = 'tumour_sample_taken')
            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
    LEFT JOIN ( request_lab_test_results tr3 JOIN lab_tests lt3
        on tr3.lab_test_id = lt3.id and lt3.test_name = 'first_outcome')
            on tr3.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr4 JOIN lab_tests lt4
        on tr4.lab_test_id = lt4.id and lt4.test_name = 'second_outcome')
            on tr4.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr5 JOIN lab_tests lt5
        on tr5.lab_test_id = lt5.id and lt5.test_name = 'withdrawal_option')
            on tr5.request_id = r.id
WHERE sc.name = 'Cancer'
    and NOT pc.unit_number ='UNKNOWN'
    and ( tr1.lab_test_id IS NULL OR tr2.lab_test_id IS NULL )
    and ( tr3.result IS NULL or tr3.result NOT IN ('Declined', 'No longer eligible') )
    and ( tr4.result IS NULL or tr4.result NOT IN ('Declined', 'No longer eligible') )
    and ( tr5.result IS NULL or NOT tr5.result = 'Full' )
ORDER BY r.id

[genomics_slf_haemonc]
SELECT /* participant ID allocated and NOT ( sample_taken + bm_sample_taken results ) */
    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 JOIN screen_category sc
        on s.category_id = sc.id ) on ris.screen_id = s.id )
            on ris.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr1 JOIN lab_tests lt1
        on tr1.lab_test_id = lt1.id and lt1.test_name = 'bm_sample_taken')
            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
    LEFT JOIN ( request_lab_test_results tr3 JOIN lab_tests lt3
        on tr3.lab_test_id = lt3.id and lt3.test_name = 'first_outcome')
            on tr3.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr4 JOIN lab_tests lt4
        on tr4.lab_test_id = lt4.id and lt4.test_name = 'second_outcome')
            on tr4.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr5 JOIN lab_tests lt5
        on tr5.lab_test_id = lt5.id and lt5.test_name = 'withdrawal_option')
            on tr5.request_id = r.id
WHERE sc.name = 'HaemOnc'
    and NOT pc.unit_number = 'UNKNOWN'
    and ( tr1.lab_test_id IS NULL OR tr2.lab_test_id IS NULL )
    and ( tr3.result IS NULL or tr3.result NOT IN ('Declined', 'No longer eligible') )
    and ( tr4.result IS NULL or tr4.result NOT IN ('Declined', 'No longer eligible') )
    and ( tr5.result IS NULL or NOT tr5.result = 'Full' )

[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',
    tr1.result AS 'family_id',
    tr2.result 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 ( genomics.request_lab_test_results tr1 JOIN genomics.lab_tests lt1
  		on tr1.lab_test_id = lt1.id and lt1.test_name = 'family_id' )
            on tr1.request_id = r.id
    LEFT JOIN ( genomics.request_lab_test_results tr2 JOIN genomics.lab_tests lt2
  		on tr2.lab_test_id = lt2.id and lt2.test_name = 'total_samples' )
            on tr2.request_id = r.id
WHERE r.id IN (??)
GROUP BY r.id

[genomics_sample_report_rare_disease]
SELECT r.id, r.request_number, r.year, p.last_name, p.first_name, pc.unit_number,
    rs.display_name as 'location', tr1.result as 'sample_taken',
	MAX(CASE WHEN lt2.test_name = 'total_samples' THEN tr2.result END) AS 'total_samples',
	MAX(CASE WHEN lt2.test_name = 'family_id'     THEN tr2.result END) AS 'family_id',
	MAX(CASE WHEN lt2.test_name = 'paxgene_rna'   THEN 1 ELSE 0 END) AS 'paxgene_rna', /* just need truth */
	MAX(CASE WHEN lt2.test_name = 'sst'           THEN 1 ELSE 0 END) AS 'sst',
	MAX(CASE WHEN lt2.test_name = 'pst'           THEN 1 ELSE 0 END) AS 'pst'
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 ( request_lab_test_results tr1 JOIN lab_tests lt1
		on tr1.lab_test_id = lt1.id and lt1.test_name = 'sample_taken' )
            on tr1.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr2 JOIN lab_tests lt2 on tr2.lab_test_id = lt2.id )
            on tr2.request_id = r.id /* for MAX CASE's */
WHERE s.description = 'Rare disease'
    AND tr1.result REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}' /* avoids warnings on non-date values */
    AND DATE(tr1.result) BETWEEN ? and ?
GROUP BY r.id

[genomics_sample_report_cancer]
SELECT r.id, r.request_number, r.year, p.last_name, p.first_name, pc.unit_number,
    rs.display_name as 'location', s.description as 'cancer_type',
    tr2.result as 'sample_taken_date', IFNULL(tr4.result, 'UNKNOWN') as 'tumour_qc_date',
    IFNULL( MAX(CASE WHEN lt5.test_name = 'clinic_sample_type' THEN tr5.result END),
        'NONE' ) AS 'clinic_sample_type',
    MAX(CASE WHEN lt5.test_name = 'ccft_dna' THEN 'Y' ELSE 'N' END) AS 'ccft_dna'
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
	 	JOIN screen_category sc on s.category_id = sc.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 = 'tumour_lab_number' )
            on tr1.request_id = r.id
    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
    LEFT JOIN ( request_lab_test_results tr3 JOIN lab_tests lt3
		on tr3.lab_test_id = lt3.id and lt3.test_name = 'unsent_sample_reason' )
            on tr3.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr4 JOIN lab_tests lt4
		on tr4.lab_test_id = lt4.id and lt4.test_name = 'tumour_qc_date' )
            on tr4.request_id = r.id
    LEFT JOIN ( request_lab_test_results tr5 JOIN lab_tests lt5 on tr5.lab_test_id = lt5.id )
            on tr5.request_id = r.id /* for MAX CASE's */
WHERE sc.name = 'Cancer'
    AND tr3.result IS NULL /* unsent sample reason */
    AND tr2.result REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}' /* avoids warnings on non-date values */
    AND DATE(tr2.result) BETWEEN ? and ?
GROUP BY r.id

[genomics_recruitment_report]
SELECT
    r.id, r.request_number, r.year,	p.last_name, p.first_name, p.nhs_number,
    pc.unit_number, rs.display_name as 'location', s.description as 'presentation',
    DATE(r.created_at) as 'registered', po.description as 'organisation',
    MAX(CASE WHEN lt.test_name = 'family_id' THEN tr.result END) AS 'family_id',
    MAX(CASE WHEN lt.test_name = 'consent_date' THEN DATE(tr.result) END) AS 'consent_date',
    MAX(CASE WHEN lt.test_name = 'sample_taken' THEN DATE(tr.result) END) AS 'sample_taken',
    MAX(CASE WHEN lt.test_name = 'blood_summary_qc' THEN tr.result END) AS 'blood_summary_qc',
    MAX(CASE WHEN lt.test_name = 'tumour_sample_taken' THEN DATE(tr.result) END) AS 'tumour_sample_taken',
    MAX(CASE WHEN lt.test_name = 'clinic_sample_type' THEN tr.result END) AS 'clinic_sample_type',
    MAX(CASE WHEN lt.test_name = 'tumour_summary_qc' THEN tr.result END) AS 'tumour_summary_qc',
    MAX(CASE WHEN lt.test_name = 'dna_dispatched' THEN DATE(tr.result) END) AS 'dna_dispatched',
    MAX(CASE WHEN lt.test_name = 'omics_dispatched' THEN DATE(tr.result) END) AS 'omics_dispatched',
    IFNULL( MAX(CASE WHEN lt.test_name = 'withdrawal_option' THEN tr.result END), 'No') AS 'withdrawal_option',
	MAX(CASE WHEN lt.test_name = 'paxgene_rna' THEN 1 ELSE 0 END) AS 'paxgene_rna', /* just need truth */
	MAX(CASE WHEN lt.test_name = 'sst'         THEN 1 ELSE 0 END) AS 'sst',
	MAX(CASE WHEN lt.test_name = 'pst'         THEN 1 ELSE 0 END) AS 'pst',
	MAX(CASE WHEN lt.test_name = 'ccft_dna'    THEN 1 ELSE 0 END) AS 'ccft_dna'
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 JOIN parent_organisations po on rs.parent_organisation_id = po.id )
	 	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
    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 DATE(r.created_at) BETWEEN ? and ?
GROUP BY r.id