[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_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_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
[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 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 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 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_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',
rsv.`time` as 'auth_datetime'
FROM requests r
JOIN request_status_view rsv on rsv.request_id = r.id
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 rsv.`action` = 'authorised'
and rsv.`time` > '2016-06-06' /* date commenced - don't need 14K redundant db entries */
GROUP BY r.id /* in case of duplicates in request_status_view */
[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]
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 (??)
[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 = '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'
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]
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,
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
