[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 = ?

[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_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, 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)
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 (??)
ORDER BY d.name, r.id

[yhhn_potential_trial_cases]
SELECT rv.*, CONCAT_WS('/', rv.request_number, rv.year - 2000) as 'hmds_ref'
FROM request_report_patient_view rv
	JOIN request_history rh on rh.request_id = rv.id
	JOIN parent_organisations po on rv.parent_code = po.parent_code
	JOIN local_network_locations nl on po.id = nl.parent_id
WHERE rh.`action` = 'authorised'
	AND (
        ( /* new diagnoses: */
            rv.`status` = 'new'
            AND (
                ( rv.icdo3 = '9673/3' AND rv.age >= 65 ) /* mantle cell lymphoma */
                OR ( rv.diagnosis IN ( /* t-cell lymphoma */
                        'Angioimmunoblastic T-cell lymphoma',
                        'Anaplastic large cell lymphoma ALK negative', /* not ALK+ */
                        'Anaplastic large cell lymphoma of T/null type',
                        'Peripheral T-cell lymphoma - common; unspecified'                        
                   ) AND rv.age >= 18 )
                OR ( rv.diagnosis rlike 'Classical Hodgkin lymphoma' AND rv.age >= 60 )
            )
        )
        OR ( /* relapse diagnoses: */
            rv.`status` = 'relapse' /* follicular/marginal zone lymphoma */
            AND ( rv.icdo3 = '9690/3' OR rv.diagnosis RLIKE 'marginal zone lymphoma' )
            AND rv.age >= 18
        )
        OR ( /* any diagnosis status: */
            rv.diagnosis IN ('Primary cutaneous anaplastic large cell lymphoma',
                'Mycosis fungoides')
            AND rv.age >= 18
        )
    )
    AND DATE(rh.time) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)

[awaiting_final_diagnosis]
SELECT rv.*, DATE(rh.time) as 'auth_date'
FROM request_report_patient_view rv
	JOIN request_history rh on rh.request_id = rv.id
WHERE rh.`action` = 'authorised'
	AND rv.diagnosis = 'awaiting final diagnosis'
   AND DATE(rh.time) < DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY DATE(rh.time)