[granulomatous_inflammation_status_alert]
SELECT
p.last_name,
p.first_name,
p.middle_name,
p.dob,
p.nhs_number,
pc.unit_number,
rs.display_name as 'location',
ref.name as 'referrer',
req.id as 'request_id',
req.request_number,
req.year,
req.created_at,
rr.status,
d.name as 'diagnosis'
FROM
requests req
join patient_case pc on req.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 req.referrer_department_id = rd.id
join referrers ref on rd.referrer_id = ref.id
join request_history h on h.request_id = req.id
join request_report rr on rr.request_id = req.id
join diagnoses d on rr.diagnosis_id = d.id
join request_lab_test_status rlts on rlts.request_id = req.id
join lab_tests lt on rlts.lab_test_id = lt.id
join lab_test_status_options so on rlts.status_option_id = so.id
WHERE d.name = 'Granulomatous inflammation'
and lt.field_label = 'TB culture'
and so.description = 'complete'
and rs.organisation_code like 'RR8%'
and h.action = 'authorised'
and DATE(h.time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
[paediatrics_status_alert]
/* combined into juvenile_diagnosis_status_alert
SELECT
p.last_name,
p.first_name,
p.middle_name,
p.dob,
p.nhs_number,
pc.unit_number,
rs.display_name as 'location',
ref.name as 'referrer',
req.id as 'request_id',
req.request_number,
req.year,
req.created_at,
rr.status,
d.name as 'diagnosis'
FROM
requests req
join patient_case pc on req.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 req.referrer_department_id = rd.id
join referrers ref on rd.referrer_id = ref.id
join request_history h on h.request_id = req.id
join request_report rr on rr.request_id = req.id
join diagnoses d on rr.diagnosis_id = d.id
WHERE rr.status IN ('new','relapsed')
and p.dob IS NOT NULL
and YEAR(CURRENT_DATE()) - YEAR(dob) -
CASE WHEN MONTH(CURRENT_DATE) > MONTH(dob) THEN 0
WHEN MONTH(CURRENT_DATE) < MONTH(dob) THEN 1
WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(dob) THEN 1
ELSE 0 END < 18
and rs.organisation_code like 'RR8%'
and h.action = 'authorised'
and date(h.time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
*/
[young_adult_status_alert]
/* combined into juvenile_diagnosis_status_alert
SELECT
p.last_name,
p.first_name,
p.middle_name,
p.dob,
p.nhs_number,
pc.unit_number,
rs.display_name as 'location',
ref.name as 'referrer',
req.id as 'request_id',
req.request_number,
req.year,
req.created_at,
rr.status,
d.name as 'diagnosis'
FROM
requests req
join patient_case pc on req.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 req.referrer_department_id = rd.id
join referrers ref on rd.referrer_id = ref.id
join request_history h on h.request_id = req.id
join request_report rr on rr.request_id = req.id
join diagnoses d on rr.diagnosis_id = d.id
WHERE rr.status IN ('new','relapsed')
and p.dob IS NOT NULL
and YEAR(CURRENT_DATE()) - YEAR(dob) -
CASE WHEN MONTH(CURRENT_DATE) > MONTH(dob) THEN 0
WHEN MONTH(CURRENT_DATE) < MONTH(dob) THEN 1
WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(dob) THEN 1
ELSE 0 END BETWEEN 13 AND 25
and rs.organisation_code like 'RR8%'
and h.action = 'authorised'
and date(h.time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
*/
[juvenile_diagnosis_status_alert]
SELECT
p.last_name,
p.first_name,
p.middle_name,
p.dob,
YEAR(CURRENT_DATE()) - YEAR(p.dob) -
( CASE WHEN MONTH(CURRENT_DATE) > MONTH(p.dob) THEN 0
WHEN MONTH(CURRENT_DATE) < MONTH(p.dob) THEN 1
WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(p.dob) THEN 1
ELSE 0 END ) as 'age',
p.nhs_number,
pc.unit_number,
rs.display_name as 'location',
ref.name as 'referrer',
req.id as 'request_id',
req.request_number,
req.year,
req.created_at,
rr.status,
d.name as 'diagnosis'
FROM
requests req
join patient_case pc on req.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 local_network_locations lnl on rs.parent_organisation_id = lnl.parent_id
join referrer_department rd on req.referrer_department_id = rd.id
join referrers ref on rd.referrer_id = ref.id
join request_history h on h.request_id = req.id
join request_report rr on rr.request_id = req.id
join diagnoses d on rr.diagnosis_id = d.id
WHERE rr.status IN ('new','relapse')
and p.dob IS NOT NULL
and ( YEAR(CURRENT_DATE()) - YEAR(p.dob) -
( CASE WHEN MONTH(CURRENT_DATE) > MONTH(p.dob) THEN 0
WHEN MONTH(CURRENT_DATE) < MONTH(p.dob) THEN 1
WHEN DAYOFMONTH(CURRENT_DATE) < DAYOFMONTH(p.dob) THEN 1
ELSE 0 END ) ) <= 25
/* and rs.organisation_code like 'RR8%' # using local_network_locations now */
and h.action = 'authorised'
and DATE(h.time) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)
[new_remodlb_cases_alert]
SELECT
CONCAT( 'H', r.`request_number`, '/', r.year - 2000 ),
p.`last_name`,
p.`first_name`,
DATE_FORMAT(p.`dob`,'%d/%m/%Y'),
pc.`unit_number`,
p.`nhs_number`,
rs.`display_name`
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)
WHERE DATE(r.created_at) = DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY)
and ct.trial_name = 'SCTU REMoDL-B' /* 'HMDS outreach' - for test */
ORDER BY r.`year`, r.`request_number`
[histopathology_referrals]
SELECT r.request_number, r.year
FROM requests r
JOIN request_report rr on rr.request_id = r.id
JOIN diagnoses d on rr.diagnosis_id = d.id
JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
WHERE d.name = 'Referred to histopathology'
and DATE(rh.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)
[new_pnh_alert]
SELECT
r1.`id` as 'request_id',
r1.`request_number`,
r1.`year`,
r1.`created_at`,
p.`last_name`,
p.`first_name`,
p.`dob`,
p.`nhs_number`,
pc.`unit_number`,
d.name as 'diagnosis',
rs.display_name as 'location',
rr.status,
r2.name as 'referrer'
FROM requests r1
JOIN patient_case pc on (r1.`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 rr on (rr.request_id = r1.id)
JOIN diagnoses d on (rr.diagnosis_id = d.id)
JOIN request_history rh on (rh.request_id = r1.id)
JOIN referrer_department rd on (r1.referrer_department_id = rd.id)
JOIN referrers r2 on (rd.referrer_id = r2.id)
WHERE
d.`name` = 'PNH'
and rr.status = 'new'
and rh.action = 'authorised'
and DATE(rh.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)
[myelomaX_data_requests]
SELECT
r.id,
r.`request_number`,
r.year,
DATE_FORMAT(r.created_at, '%d.%b.%Y') as 'registered',
p.`last_name`,
p.`first_name`,
pt.`trial_number`,
s.description as 'presentation',
fish.results_summary as 'FISH result',
DATE_FORMAT(fish.time, '%d.%b.%Y') as 'FISH date',
flow.results_summary as 'Flow result',
DATE_FORMAT(flow.time, '%d.%b.%Y') as 'Flow date'
FROM requests r
JOIN `patient_case` pc on (r.`patient_case_id` = pc.id)
JOIN patients p on (pc.`patient_id` = p.id)
LEFT JOIN ( `patient_trial` pt
JOIN `clinical_trials` ct on (pt.trial_id = ct.id) )
on (pt.`patient_id` = p.id and ct.`trial_name` = 'NCRI Myeloma X')
JOIN `request_initial_screen` ris on (ris.`request_id` = r.id)
JOIN screens s on (ris.`screen_id` = s.id)
LEFT JOIN ( `request_result_summaries` fish
JOIN lab_sections ls1 on (fish.`lab_section_id` = ls1.id) )
on (fish.`request_id` = r.id and ls1.`section_name` = 'FISH')
LEFT JOIN ( `request_result_summaries` flow
JOIN lab_sections ls2 on (flow.`lab_section_id` = ls2.id) )
on (flow.`request_id` = r.id and ls2.`section_name` = 'Flow cytometry')
WHERE s.description IN (
/* 'Myeloma X - presentation', # HILIS 3 term */
/* 'Myeloma X - follow-up', # HILIS 3 term */
'Myeloma trial presentation',
'Myeloma trial follow-up'
)
and (
(
MONTH(fish.time) = MONTH( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) ) and
YEAR(fish.time) = YEAR( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) )
)
or (
MONTH(flow.time) = MONTH( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) ) and
YEAR(flow.time) = YEAR( DATE_SUB(CURRENT_DATE, INTERVAL ? MONTH) )
)
)
ORDER BY r.year, r.request_number
[resultable_fish_probe_names]
SELECT t2.field_label, t2.id
FROM lab_tests t2
JOIN lab_sections t3 on (t2.`lab_section_id` = t3.id)
WHERE t3.`section_name` = 'FISH'
and t2.`has_results` = 'yes'
[myelomaX_fish_results]
SELECT
t2.`field_label`,
t1.result
FROM request_lab_test_results t1
JOIN lab_tests t2 on (t1.lab_test_id = t2.id)
JOIN lab_sections t3 on (t2.`lab_section_id` = t3.id)
WHERE t3.`section_name` = 'FISH'
and t1.request_id = ?
[get_reporter_from_request_id]
SELECT
t2.last_name,
t2.first_name
FROM
request_history t1
join users t2 on (t1.user_id = t2.id)
WHERE action = 'reported'
and request_id = ?
[request_specimens]
SELECT
t2.description
FROM
request_specimen t1
join specimens t2 on (t1.specimen_id = t2.id)
WHERE
t1.request_id = ?
[dlbcl_or_burkitt_diagnosis]
SELECT
rr.request_id,
r.request_number,
r.year
FROM requests r
join request_report rr on (rr.request_id = r.id)
join diagnoses d on (rr.diagnosis_id = d.id)
WHERE rr.updated_at = DATE_SUB(CURDATE(), INTERVAL ? DAY)
AND d.name = 'DLBCL or Burkitt lymphoma - further tests pending'
[expire_user_accounts]
UPDATE users SET active = 'no'
WHERE last_login < DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH)
AND active = 'yes'
[lab_param_monitoring_data]
/* converted to RDBO method to allow placeholders in s.description field
SELECT
DATE(r.`created_at`) as 'date',
rs.`results_summary` as 'result'
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 on ris.`request_id` = r.id
JOIN `screens` s on ris.`screen_id` = s.id
JOIN `request_result_summaries` rs on rs.`request_id` = r.id
JOIN `lab_sections` ls on rs.`lab_section_id` = ls.id
WHERE p.`id` = ?
AND ls.`section_name` = ?
AND s.`description` = ? # need IN (??)
ORDER BY r.id
*/
[national_code_unknown]
SELECT
r1.`request_number`,
r1.`year`,
r1.`created_at`,
rs.`display_name`,
rs.`organisation_code`
FROM
`referrer_department` rd
JOIN `requests` r1 ON r1.`referrer_department_id` = rd.`id`
JOIN `referrers` r2 ON rd.`referrer_id` = r2.`id`
JOIN `patient_case` pc on r1.`patient_case_id` = pc.`id`
JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
LEFT JOIN `request_trial` rt ON rt.`request_id` = r1.`id`
WHERE (
r2.`national_code` like '%999998' OR
rs.`organisation_code` IN ('X99999','V81999')
)
and r1.`created_at` >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
and rt.`request_id` IS NULL
ORDER BY r1.year, r1.request_number
[teaching_cases]
SELECT
r.id,
r.request_number,
r.year,
d.name as 'diagnosis',
rr.status,
DATE(rh.`time`) as 'auth_date'
FROM requests r
JOIN request_report rr on rr.request_id = r.id
JOIN diagnoses d on rr.diagnosis_id = d.id
JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
JOIN request_option ro on ro.request_id = r.id
JOIN additional_options ao on ro.option_id = ao.id
WHERE DATE(rh.`time`) >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
AND ao.option_name = 'teaching'
[activity_data]
SELECT
req.`id`,
req.`request_number`,
req.`year`,
req.`created_at`,
IF(HOUR(req.`created_at`) BETWEEN 8 and 17, 'N', 'Y') as 'on_call',
p.`last_name`,
p.`first_name`,
p.`middle_name`,
p.`nhs_number`,
p.`gender`,
p.`dob`,
YEAR(req.`created_at`) - YEAR(p.`dob`)
- (DATE_FORMAT(req.`created_at`, '00-%m-%d') < DATE_FORMAT(p.`dob`, '00-%m-%d'))
AS 'age',
pc.`unit_number`,
src.`organisation_code`,
ref.`national_code` as 'referrer_code',
rd.`hospital_department_code`,
src.`display_name`,
s1.`sample_code`,
s1.`description` as 'sample_description',
IF(rt.`request_id`,'ST','01') as 'category',
s2.`description` as 'presentation',
ct.trial_name
FROM `requests` req
JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
JOIN `patients` p on pc.`patient_id` = p.`id`
JOIN `referral_sources` src on pc.`referral_source_id` = src.`id`
JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
JOIN `referrers` ref on rd.`referrer_id` = ref.`id`
JOIN `request_specimen` rs on rs.`request_id` = req.`id`
JOIN `specimens` s1 on rs.`specimen_id` = s1.`id`
LEFT JOIN `request_initial_screen` ris JOIN `screens` s2 on (ris.`screen_id` = s2.`id`)
on (ris.`request_id` = req.`id`)
LEFT JOIN `request_trial` rt JOIN `clinical_trials` ct on (rt.trial_id = ct.id)
on (rt.`request_id` = req.`id`)
WHERE MONTH(req.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and req.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
ORDER BY req.`year`, req.`request_number`
[activity_patient_is_private]
/* replaced with activity_private_patients
SELECT 1
FROM request_option ro
JOIN additional_options ao on ro.option_id = ao.id
WHERE ro.request_id = ?
and ao.option_name = 'private'
*/
[activity_private_patients]
SELECT r.id, 1
FROM requests r
JOIN ( request_option ro JOIN additional_options ao on ro.option_id = ao.id )
on ro.request_id = r.id
WHERE MONTH(r.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and r.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and ao.option_name = 'private'
[activity_request_has_marrow]
/* replaced with activity_bone_marrow_requests
SELECT 1
FROM requests r
JOIN ( request_specimen rs join specimens s on rs.specimen_id = s.id )
on rs.request_id = r.id
WHERE rs.request_id = ? and s.sample_code IN ('BMA', 'BMAT')
*/
[activity_bone_marrow_requests]
SELECT r.id, 1
FROM requests r
JOIN ( request_specimen rs join specimens s on rs.specimen_id = s.id )
on rs.request_id = r.id
WHERE MONTH(r.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and r.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and s.sample_code IN ('BMA', 'BMAT')
[activity_molecular_sequencing]
SELECT DISTINCT(r.id), 1
FROM requests r
JOIN ( request_lab_test_status lts JOIN lab_tests lt on lts.lab_test_id = lt.id )
on lts.request_id = r.id
JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
on rs.request_id = r.id
WHERE MONTH(r.`created_at`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and r.`year` = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and s.sample_code = 'PB'
and lt.test_name IN (??)
[trial_cases]
SELECT
r.request_number,
r.year,
ct.trial_name,
s1.description,
DATE_FORMAT(r.created_at, '%d.%b.%Y'),
GROUP_CONCAT(s2.sample_code)
FROM requests r
JOIN request_initial_screen ris on (ris.request_id = r.id)
JOIN screens s1 on (ris.screen_id = s1.id)
JOIN request_trial rt on (rt.request_id = r.id)
JOIN clinical_trials ct on (rt.trial_id = ct.id)
JOIN ( request_specimen rs
JOIN specimens s2 on (rs.specimen_id = s2.id) ) on (rs.request_id = r.id)
WHERE ct.trial_name IN (??)
and r.created_at BETWEEN DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
and DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY r.id
[ncg_pnh]
SELECT s2.sample_code, COUNT(s2.sample_code)
FROM requests r
JOIN ( request_initial_screen ris JOIN screens s1 on ris.screen_id = s1.id )
on ris.request_id = r.id
JOIN ( request_specimen rs JOIN specimens s2 on rs.specimen_id = s2.id )
on rs.request_id = r.id
JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'screened' )
WHERE s1.description like 'NCG PNH %'
and s2.sample_code IN ('PB', 'BMA', 'BMAT')
and DATE(rh.time) BETWEEN DATE_SUB(CURDATE(), INTERVAL ? MONTH)
and DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY s2.id
[outreach_patients]
SELECT
p.last_name,
p.first_name,
p.dob,
pc.unit_number,
p.nhs_number,
DATE(r.created_at),
rs1.display_name,
s2.description,
s.sample_code
FROM requests r
JOIN ( patient_case pc JOIN patients p on (pc.patient_id = p.id) )
on (r.patient_case_id = pc.id)
JOIN referral_sources rs1 on (pc.referral_source_id = rs1.id)
JOIN ( request_specimen rs2 JOIN specimens s on (rs2.specimen_id = s.id) )
on (rs2.request_id = r.id)
JOIN (request_initial_screen ris JOIN screens s2 on ris.screen_id = s2.id )
on ris.request_id = r.id
WHERE s2.description = 'Outreach'
and s.sample_code <> 'CMP'
and YEAR(r.created_at) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
and MONTH(r.created_at) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
ORDER BY r.created_at
[_new_referrers]
/* discontinued - replaced by recent_referrers, seen_referrers & new_referrers
SELECT
rd.`id` as 'referrer_department_id',
r.`name`,
r.`national_code`,
hd.`display_name` as 'department',
po.`description` as 'organisation',
po.`parent_code`
FROM `referrer_department` rd
JOIN `referrers` r on rd.`referrer_id` = r.`id`
JOIN `hospital_departments` hd on rd.`hospital_department_code` = hd.`id`
JOIN `parent_organisations` po on rd.`parent_organisation_id` = po.`id`
WHERE rd.`id` IN (
SELECT DISTINCT(rd.`id`)
FROM `requests` req
JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
WHERE req.`created_at` >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
)
*/
[recent_referrers]
SELECT DISTINCT(r2.national_code)
FROM requests r1
JOIN referrer_department rd on r1.referrer_department_id = rd.id
JOIN referrers r2 on rd.referrer_id = r2.id
LEFT JOIN request_history rh on ( rh.request_id = r1.id
and rh.`action` REGEXP 'amended referrer' )
WHERE DATE(r1.created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
OR DATE(rh.time) >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
[referrer_department_map]
/* discontinued - replaced by recent_referrers, seen_referrers & new_referrers
SELECT DISTINCT(rd.`id`), 1
FROM `requests` req
JOIN `referrer_department` rd on req.`referrer_department_id` = rd.`id`
WHERE req.`created_at` < DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
*/
[seen_referrers]
SELECT 1 FROM requests r1
JOIN referrer_department rd on r1.referrer_department_id = rd.id
JOIN referrers r2 on rd.referrer_id = r2.id
WHERE r2.national_code = ?
AND DATE(r1.created_at) < DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
[new_referrers]
SELECT
r.`name`,
r.`national_code`,
hd.`display_name` as 'department',
po.`description` as 'organisation',
po.`parent_code`
FROM `referrer_department` rd
JOIN `referrers` r on rd.`referrer_id` = r.`id`
JOIN `hospital_departments` hd on rd.`hospital_department_code` = hd.`id`
JOIN `parent_organisations` po on rd.`parent_organisation_id` = po.`id`
WHERE r.national_code IN (??)
[new_referrer]
SELECT
r.`name`,
r.`national_code`,
hd.`display_name` as 'department',
po.`description` as 'organisation',
po.`parent_code`
FROM `referrer_department` rd
JOIN `referrers` r on rd.`referrer_id` = r.`id`
JOIN `hospital_departments` hd on rd.`hospital_department_code` = hd.`id`
JOIN `parent_organisations` po on rd.`parent_organisation_id` = po.`id`
WHERE r.national_code = ?
[new_location]
SELECT rs.display_name, rs.organisation_code, rt.description
FROM referral_sources rs
JOIN parent_organisations po on rs.parent_organisation_id = po.id
JOIN referral_types rt on rs.referral_type_id = rt.id
WHERE rs.organisation_code = ?
[new_national_code]
SELECT national_code, type
FROM new_national_code
WHERE DATE(created_at) = DATE_SUB(CURDATE(), INTERVAL ? DAY)
[hodgkin]
SELECT
p.`last_name`,
p.`first_name`,
p.`dob`,
pc.`unit_number`,
p.`nhs_number`,
rs.`display_name` as 'location',
d.`name` as 'diagnosis'
FROM `requests` req
JOIN `patient_case` pc on req.`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 `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
JOIN `request_report` rr on rr.`request_id` = req.`id`
JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
JOIN `icdo_category` ic on ic.`icdo3` = d.`icdo3`
JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
JOIN `request_history` rh on rh.`request_id` = req.`id`
WHERE dc.`description` = 'hodgkin'
and rh.`action` = 'authorised'
and rr.`status` = 'new'
and po.`parent_code` IN ('RAE','RR8')
and DATE(rh.`time`) >= DATE_SUB(NOW(), INTERVAL ? DAY)
[ex-hodgkin]
SELECT
p.`last_name`,
p.`first_name`,
p.`dob`,
pc.`unit_number`,
p.`nhs_number`,
rs.`display_name` as 'location',
d.`name` as 'diagnosis'
FROM `requests` req
JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
JOIN `patients` p on pc.`patient_id` = p.`id`
JOIN `request_report` rr on rr.`request_id` = req.`id`
JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
JOIN `icdo_category` ic on ic.`icdo3` = d.`icdo3`
JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
WHERE req.`id` IN (
SELECT req.`id`
FROM `requests` req
JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
JOIN `request_report` rr on rr.`request_id` = req.`id`
JOIN `request_diagnosis_history` rh on rh.`request_id` = req.`id`
JOIN `diagnoses` d on rh.`diagnosis_id` = d.`id`
JOIN `icdo_category` ic on ic.`icdo3` = d.`icdo3`
JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
WHERE dc.`description` = 'hodgkin'
and rr.`status` = 'new'
and po.`parent_code` IN ('RAE','RR8')
and rh.`time` >= DATE_SUB(NOW(), INTERVAL ? DAY)
)
and dc.`description` <> 'hodgkin'
[mds_diagnoses]
SELECT
CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
UPPER(p.`last_name`),
CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
pc.`unit_number`,
p.`nhs_number`,
p.`dob`,
rs.`display_name`,
d.`name`,
DATE(rh.`time`),
IF(rr.`status` = 'default', 'follow-up', rr.`status`)
FROM `requests` req
JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
JOIN `patients` p on pc.`patient_id` = p.`id`
JOIN `request_report` rr on rr.`request_id` = req.`id`
JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
JOIN `icdo_category` ic on ic.`icdo3` = d.`icdo3`
JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
JOIN `request_history` rh on rh.`request_id` = req.`id`
WHERE dc.`description` = 'Myelodysplastic'
and rh.`action` = 'authorised'
and rh.`time` >= DATE_SUB(NOW(), INTERVAL ? DAY)
ORDER BY rh.`time`, req.`year`, req.`request_number`
[myeloid_diagnoses]
SELECT
CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
UPPER(p.`last_name`),
CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
pc.`unit_number`,
p.`nhs_number`,
p.`dob`,
rs.`display_name`,
d.`name`,
DATE(rh.`time`),
IF(rr.`status` = 'default', 'follow-up', rr.`status`)
FROM `requests` req
JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
JOIN `referral_sources` rs on pc.`referral_source_id` = rs.`id`
JOIN `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
JOIN `patients` p on pc.`patient_id` = p.`id`
JOIN `request_report` rr on rr.`request_id` = req.`id`
JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
JOIN `icdo_category` ic on ic.`icdo3` = d.`icdo3`
JOIN `diagnostic_categories` dc on ic.`diagnostic_category_id` = dc.`id`
JOIN `request_history` rh on rh.`request_id` = req.`id`
WHERE dc.`description` IN ('Myelodysplastic','Myeloproliferative')
and rh.`action` = 'authorised'
and po.`parent_code` IN ('RR8','RWY')
and MONTH(rh.`time`) = ? and YEAR(rh.`time`) = ?
ORDER BY rh.`time`, req.`year`, req.`request_number`
[register_log]
SELECT u.id, u.username, COUNT(*) as f
FROM request_history rh
JOIN users u on (rh.user_id = u.id)
WHERE `action` = 'registered'
AND YEAR(rh.time) = ? AND MONTH(rh.time) = ?
GROUP BY u.username
ORDER BY f DESC
[error_code_A_requests]
SELECT DISTINCT(request_id)
FROM request_history
WHERE YEAR(`time`) = ? and MONTH(`time`) = ?
and (`action` LIKE 'deleted%' OR `action` = 'recorded error code A' )
[error_code_A_data]
/* register_and_error_log query - not used anymore
SELECT request_id, `action`, user_id
FROM request_history
WHERE YEAR(`time`) = ? and MONTH(`time`) = ?
and `action` regexp '^(deleted|modified)'
and request_id IN (%PARAMS%)
*/
[error_codes]
SELECT UCASE(t2.code), t2.description, count(*)
FROM request_error_code t1
JOIN error_codes t2 on (t1.error_code_id = t2.id)
WHERE YEAR(t1.`time`) = ? and MONTH(t1.`time`) = ?
GROUP BY t2.id
ORDER BY t2.code
[error_log]
/* # discontinued after hilis3
SELECT
r.id as 'request_id',
r.request_number,
r.year,
UCASE(u.username) as 'modifier',
rh1.action,
rh2.action as 'err_code_action',
rh2.id as 'err_history_id',
rh1.time
FROM requests r
JOIN request_history rh1 on (rh1.request_id = r.id)
JOIN request_history rh2 on (
rh1.time = rh2.time
and rh1.request_id = rh2.request_id
and rh2.`action` like 'recorded error code %')
JOIN users u on (rh1.user_id = u.id)
WHERE DATE(rh1.`time`) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
and (
rh1.action regexp 'modified (%REGEXP%)'
or rh1.action = 'deleted' )
*/
[request_history_error_codes]
SELECT
r.id as 'request_id',
r.request_number,
r.year,
UCASE(u.username) as 'modifier',
rh.id as 'request_history_id',
rh.action,
rh.time
FROM requests r
JOIN request_history rh on (rh.request_id = r.id)
JOIN users u on (rh.user_id = u.id)
WHERE DATE(rh.`time`) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
and rh.`action` like 'recorded error code %'
[error_log_patient_edits]
/* # not using
SELECT *
FROM patient_edits pe
JOIN users u on (pe.user_id = u.id)
JOIN error_codes ec on (pe.error_code_id = ec.id)
WHERE DATE(pe.`time`) >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
*/
[registration_user_map]
SELECT user_id, COUNT(*)
FROM request_history
WHERE `action` = 'registered'
and request_id IN (??)
GROUP BY user_id
[null_nhs_number]
SELECT
CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
UPPER(p.`last_name`),
CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
p.`dob`,
pc.`unit_number`,
DATE(p.`created_at`),
rs.`display_name`
FROM `requests` req
JOIN `patient_case` pc on req.`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 `parent_organisations` po on rs.`parent_organisation_id` = po.`id`
LEFT JOIN ( `request_trial` rt
JOIN `clinical_trials` ct on rt.`trial_id` = ct.`id` )
on rt.`request_id` = req.`id`
WHERE YEARWEEK(p.`created_at`, 1) = YEARWEEK(CURRENT_DATE(), 1)
and p.`nhs_number` IS NULL
/* and po.`parent_code` = 'RR8' */
and p.`first_name` NOT IN ('LGI','PIN')
and ( ct.`trial_name` IS NULL OR ct.`trial_name` NOT IN (??) )
[cases_registered]
SELECT
CONCAT('H', req.`request_number`, '/', req.`year` - 2000),
UPPER(p.`last_name`),
CONCAT(UPPER(SUBSTRING(p.`first_name`, 1, 1)), LOWER(SUBSTRING(p.`first_name` FROM 2))),
p.`dob`,
DATE(req.`created_at`),
p.`nhs_number`
FROM `requests` req
JOIN `patient_case` pc on req.`patient_case_id` = pc.`id`
JOIN `patients` p on pc.`patient_id` = p.`id`
WHERE req.`created_at` > DATE_SUB(CURDATE(), INTERVAL ? DAY)
ORDER BY req.`year`, req.`request_number`
[time_to_authorisation_by_specimen]
SELECT
DATE(req.`created_at`) as 'registered',
DATE(rh.`time`) as 'authorised',
s.`sample_code`
FROM `requests` req
JOIN `request_history` rh on rh.`request_id` = req.`id`
JOIN `request_specimen` rs on rs.`request_id` = req.`id`
JOIN `specimens` s on rs.`specimen_id` = s.`id`
WHERE req.created_at >= date_sub(CURRENT_DATE(), INTERVAL ? MONTH)
and rh.`action` = 'authorised'
[overdue_for_authorisation]
SELECT
req.`id`,
CONCAT('H', req.`request_number`, '/', req.`year` - 2000) as 'lab_number',
DATE(req.`created_at`) as 'registered',
CONCAT( UPPER(p.`last_name`), ', ',
CONCAT( UPPER(SUBSTRING(p.`first_name`, 1, 1) ),
LOWER( SUBSTRING(p.`first_name` FROM 2) ) ) ) as 'name',
src.`display_name` as 'location',
s1.`sample_code` as 'specimen',
s2.`description`
FROM
requests `req`
JOIN `status_options` so ON (req.`status_option_id` = so.`id`)
JOIN `patient_case` pc ON (req.`patient_case_id` = pc.`id`)
JOIN `patients` p ON (pc.`patient_id` = p.`id`)
JOIN `referral_sources` src ON (pc.`referral_source_id` = src.`id`)
JOIN `request_specimen` rs ON (rs.`request_id` = req.`id`)
JOIN `specimens` s1 ON (rs.`specimen_id` = s1.`id`)
JOIN `request_initial_screen` ris ON (ris.`request_id` = req.`id`)
JOIN `screens` s2 ON (ris.`screen_id` = s2.`id`)
WHERE
so.`description` = 'reported'
ORDER BY req.`year`, req.`request_number`
[unreported_requests]
SELECT
CONCAT(t1.request_number, '/', t1.year - 2000) as 'labno',
DATE(t1.created_at) as 'registered',
t5.display_name as 'location',
t7.description as 'presentation',
DATEDIFF(CURDATE(), DATE(t1.created_at)) as 'delta'
FROM
requests t1
JOIN status_options t2 ON (t1.status_option_id = t2.id)
JOIN patient_case t3 ON (t1.patient_case_id = t3.id)
JOIN referral_sources t5 ON (t3.referral_source_id = t5.id)
JOIN request_initial_screen t6 ON (t1.id = t6.request_id)
JOIN screens t7 ON (t6.screen_id = t7.id)
WHERE
t2.description = 'screened'
and t1.created_at <= date_sub(CURDATE(), INTERVAL 7 DAY)
GROUP BY t1.id
ORDER BY t1.created_at, request_number
[unauthorised_requests]
SELECT
CONCAT(r.`request_number`, '/', r.`year` - 2000) as 'labno',
rh.`time` as 'reported',
d.`name` as 'diagnosis',
s.`description` as 'screened_as'
FROM `requests` r
JOIN `request_history` rh ON rh.`request_id` = r.`id`
JOIN `status_options` so on r.`status_option_id` = so.`id`
JOIN `request_report` rr on rr.`request_id` = r.`id`
JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
JOIN `request_initial_screen` ris on ris.`request_id` = r.`id`
JOIN `screens` s ON ris.`screen_id` = s.`id`
WHERE
so.`description` = 'reported'
and rh.`action` = 'reported'
ORDER BY rh.`time`
[unscreened_requests]
SELECT
CONCAT(r.`request_number`, '/', r.`year` - 2000) as 'labno',
DATE(r.`created_at`) as 'registered',
GROUP_CONCAT(s2.sample_code) as 'specimen',
s1.display_name as 'location'
FROM `requests` r
JOIN patient_case pc on r.patient_case_id = pc.id
JOIN referral_sources s1 on pc.referral_source_id = s1.id
JOIN request_specimen rs on rs.request_id = r.id
JOIN specimens s2 on rs.specimen_id = s2.id
LEFT JOIN `request_initial_screen` ris on ris.`request_id` = r.`id`
WHERE ris.request_id IS NULL
GROUP BY r.id
[results_and_ppm]
SELECT
req.`id` as 'request_id',
rs.`organisation_code`, /* ppm only */
p.`gender` as 'sex',
p.`last_name`,
p.`first_name`,
p.`nhs_number`,
pc.`unit_number`,
DATE_FORMAT(p.`dob`, '%d.%m.%Y') as 'dob',
p.`id` as 'patient_id', /* ppm only */
ref.`national_code` as 'referrer_code',
ref.`name` as 'referrer',
CONCAT('H',req.`request_number`,'/', req.`year` - 2000) as 'labno',
rr.`clinical_details`,
gd.`detail` as 'gross_description',
rr.`specimen_quality`,
rr.`comment`,
d.`name` as 'diagnosis'
FROM
`requests` req
JOIN `patient_case` pc on req.`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 req.`referrer_department_id` = rd.`id`
JOIN `referrers` ref on rd.`referrer_id` = ref.`id`
JOIN `request_report` rr on rr.`request_id` = req.`id`
JOIN `diagnoses` d on rr.`diagnosis_id` = d.`id`
LEFT JOIN `request_gross_description` gd on gd.`request_id` = req.`id`
WHERE req.`id` IN (??)
[results_and_ppm_request_ids]
SELECT DISTINCT(r.`id`)
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 `parent_organisations` po on rs.`parent_organisation_id` = po.`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`
LEFT JOIN `request_diagnosis_history` rdh on rdh.`request_id` = r.`id`
[incomplete_request_ids]
SELECT DISTINCT(r.`id`)
FROM `requests` r
JOIN `status_options` so ON (r.`status_option_id` = so.`id`)
JOIN `request_initial_screen` ris ON (ris.`request_id` = r.`id`)
JOIN `screens` s ON (ris.`screen_id` = s.`id`)
JOIN `request_result_summaries` rrs ON (rrs.`request_id` = r.`id`)
JOIN `lab_sections` ls ON (rrs.`lab_section_id` = ls.`id`)
WHERE so.`description` = 'authorised'
and ls.`section_name` IN ('Molecular','Cytogenetics','FISH')
and s.`description` NOT IN (??)
ORDER BY r.`year`, r.`request_number`
[authorised_requests]
SELECT t1.id, t1.updated_at
FROM requests t1
JOIN status_options t2 ON (t1.status_option_id = t2.id)
WHERE t2.description = 'authorised'
and t1.id NOT IN (??)
[unsent_diagnosis_status_alerts]
/* sending all diagnosis_status_alerts now
SELECT
r.id as 'request_id',
rs.id as 'ref_src_id',
rs.parent_organisation_id
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_report rr on (rr.request_id = r.id)
JOIN request_history rh1 on (rh1.request_id = r.id and rh1.`action` = 'authorised')
LEFT JOIN request_history rh2
on (rh2.request_id = r.id and rh2.action like 'e-mailed diagnosis status alert%' )
WHERE rr.`status` IN ('new','relapse')
and DATE(rh1.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)
and rh2.request_id IS NULL
*/
[diagnosis_status_alerts]
SELECT
r.id as 'request_id',
rs.id as 'ref_src_id',
rs.parent_organisation_id
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_report rr on (rr.request_id = r.id)
JOIN request_history rh1 on (rh1.request_id = r.id and rh1.`action` = 'authorised')
WHERE rr.`status` IN ('new','relapse')
and DATE(rh1.time) = DATE_SUB(CURDATE(), INTERVAL ? DAY)
[nycris_data]
SELECT
r.id as 'request_id',
DATE(r.created_at) as 'request_date',
p.`last_name`,
p.`first_name`,
p.`middle_name`,
p.`nhs_number`,
pc.`unit_number`,
p.`dob`,
p.`gender`,
r2.`national_code` as 'referrer_code',
r2.`name` as 'referrer',
CONCAT('H',r.`request_number`,'/', r.`year` - 2000) as 'labno',
rs2.`display_name` as 'location',
rs2.`organisation_code`,
rr.`clinical_details`,
rgd.`detail` as 'gross_description',
rr.`specimen_quality`,
rr.`clinical_details`,
rr.`comment`,
d.`name` as 'diagnosis',
d.`icdo3`,
DATE(rh.time) as 'authorisation_date'
FROM requests r
JOIN request_report rr on (rr.request_id = r.id)
JOIN diagnoses d on (rr.diagnosis_id = d.id)
JOIN request_history rh on (rh.request_id = r.id and rh.action = 'reported')
JOIN patient_case pc on (r.patient_case_id = pc.id)
JOIN patients p on (pc.patient_id = p.id)
JOIN referrer_department rd on (r.referrer_department_id = rd.id)
JOIN referrers r2 on (rd.referrer_id = r2.id)
JOIN referral_sources rs2 on (pc.referral_source_id = rs2.id)
LEFT JOIN request_gross_description rgd on (rgd.request_id = r.id)
WHERE r.id in (??)
[nycris_new_diagnosis_request_ids]
SELECT r.id
FROM requests r
JOIN referrer_department rd on (r.referrer_department_id = rd.id)
JOIN request_report rr on (rr.request_id = r.id)
JOIN request_history rh on (rh.request_id = r.id)
WHERE rr.status = 'new'
and rh.action = 'authorised'
and MONTH(rh.`time`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and YEAR(rh.`time`) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and rd.parent_organisation_id in (
SELECT parent_id FROM local_network_locations
)
[nycris_revised_diagnosis_request_ids]
SELECT DISTINCT(r.id)
FROM requests r
JOIN referrer_department rd on (r.referrer_department_id = rd.id)
JOIN request_report rr on (rr.request_id = r.id)
JOIN request_history rh on (rh.request_id = r.id)
JOIN request_diagnosis_history rdh on (rdh.request_id = r.id)
WHERE rr.status = 'new'
and rh.action = 'authorised'
and MONTH(rdh.`time`) = MONTH(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and YEAR(rdh.`time`) = YEAR(DATE_SUB(CURRENT_DATE(), INTERVAL ? MONTH))
and rd.parent_organisation_id in (
SELECT parent_id FROM local_network_locations
)
[single_use_referral_sources]
/* replaced with unique_referral_sources & unique_referral_source_request_ids
SELECT rs.id
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
GROUP BY rs.id
HAVING COUNT(rs.id) = 1
*/
[unique_referral_source_request_ids]
SELECT t1.id
FROM requests t1
LEFT JOIN request_history t2 on ( t2.request_id = t1.id
and t2.`action` REGEXP 'amended referral source' )
WHERE DATE(t1.created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
OR DATE(t2.time) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
[unique_referral_sources]
SELECT rs.*, rt.description,
MAX(DATE(r.created_at)) as 'reg_date' /* need newest date if multiple */
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 referral_types rt on rs.referral_type_id = rt.id
WHERE r.id IN (??)
GROUP BY rs.id
[new_referral_sources]
/* replaced with referral_source_seen
SELECT rs.*, rt.description
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 referral_types rt on rs.referral_type_id = rt.id
WHERE DATE(r.created_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
and rs.id in (??)
*/
[referral_source_seen]
SELECT COUNT(*)
FROM requests r
JOIN patient_case pc on r.patient_case_id = pc.id
WHERE pc.referral_source_id = ?
AND DATE(r.created_at) < ?
[turnaround_times_specimen]
/* slow, but slightly faster allocating h1 = authorised, h2 = reported */
SELECT
s1.sample_code,
s2.description,
DATE(r.created_at) as 'registered',
DATE(h1.time) as 'authorised',
DATE(h2.time) as 'reported'
FROM
requests r
JOIN ( request_specimen rs JOIN specimens s1 ON rs.specimen_id = s1.id )
ON (r.id = rs.request_id)
JOIN ( request_initial_screen ris JOIN screens s2 ON ris.screen_id = s2.id)
ON (r.id = ris.request_id)
JOIN request_history h1 ON (r.id = h1.request_id and h1.action = 'authorised')
JOIN request_history h2 ON (r.id = h2.request_id and h2.action = 'reported')
WHERE %CONSTRAINT%
[turnaround_times_specimen_to_authorisation]
/* slow, but slightly faster allocating h1 = authorised, h2 = reported */
SELECT
s1.sample_code,
s2.description,
DATE(r.created_at) as 'registered',
DATE(h1.time) as 'authorised'
FROM
requests r
JOIN ( request_specimen rs JOIN specimens s1 ON rs.specimen_id = s1.id )
ON (r.id = rs.request_id)
JOIN ( request_initial_screen ris JOIN screens s2 ON ris.screen_id = s2.id)
ON (r.id = ris.request_id)
JOIN request_history h1 ON (r.id = h1.request_id and h1.action = 'authorised')
WHERE %CONSTRAINT%
[nhs_number_compliance]
SELECT
rs.display_name as 'location',
COUNT(*) as 'total',
COUNT(p.nhs_number) as 'has_nhs_number',
ROUND(100 * COUNT(p.nhs_number) / COUNT(*), 0) as 'percent'
FROM requests r
JOIN patient_case pc ON (pc.id = r.patient_case_id)
JOIN referral_sources rs ON (pc.referral_source_id = rs.id)
JOIN patients p ON (pc.patient_id = p.id)
JOIN request_initial_screen ris ON (ris.request_id = r.id)
JOIN screens s ON (ris.screen_id = s.id)
LEFT JOIN ( request_trial rt JOIN clinical_trials ct on rt.trial_id = ct.id )
on rt.request_id = r.id
WHERE %CONSTRAINT%
GROUP BY rs.id
HAVING total > 5
ORDER BY ROUND(100 * COUNT(p.nhs_number) / COUNT(*), 0) DESC, total DESC, rs.display_name
[user_workload_stats]
SELECT
GROUP_CONCAT(DISTINCT(s.sample_code) ORDER BY s.sample_code SEPARATOR '-')
as 'specimen',
rh.action,
u.username
FROM
requests r
JOIN (request_specimen rs JOIN specimens s ON (rs.specimen_id = s.id)) ON r.id = rs.request_id
JOIN (request_history rh JOIN users u ON (rh.user_id = u.id)) ON r.id = rh.request_id
WHERE %CONSTRAINT% AND rh.action IN (??)
GROUP BY r.id, rh.action
[revised_diagnoses]
SELECT
r.id,
r.request_number,
r.year,
d1.name as 'current_diagnosis',
d2.name as 'revised_diagnosis',
u1.username as 'reporter',
u2.username as 'reviser'
FROM
requests r
JOIN request_report rr ON (rr.request_id = r.id)
JOIN request_diagnosis_history dh ON (dh.request_id = r.id)
JOIN diagnosis_change_options co ON (dh.option_id = co.id)
JOIN diagnoses d1 ON (rr.diagnosis_id = d1.id)
JOIN diagnoses d2 ON (dh.diagnosis_id = d2.id)
JOIN request_history rh ON (rh.request_id = r.id and rh.action = 'reported')
JOIN users u1 ON (rh.user_id = u1.id)
JOIN users u2 ON (dh.user_id = u2.id)
WHERE co.option_name LIKE 'error%'
and %CONSTRAINT%
ORDER BY r.year, r.request_number
[user_workload]
/* replaced by RDBO method
SELECT
u.username,
s.sample_code,
rh.action
FROM requests r
JOIN ( request_history rh JOIN users u on rh.user_id = u.id )
on ( rh.request_id = r.id )
JOIN ( request_specimen rs JOIN specimens s on rs.specimen_id = s.id )
on (rs.request_id = r.id)
WHERE
date(rh.time) >= ?
and rh.`action` = ?
*/
[unfixed_histology_specimens]
SELECT
r.id,
r.request_number,
DATE(r.created_at),
GROUP_CONCAT(s.sample_code),
d.name,
DATE(rh.time)
FROM
requests r
JOIN request_report rr ON (rr.request_id = r.id)
JOIN diagnoses d ON (rr.diagnosis_id = d.id)
JOIN request_history rh ON (rh.request_id = r.id and rh.action = 'authorised')
JOIN request_specimen rs on (rs.request_id = r.id)
JOIN specimens s on (rs.specimen_id = s.id)
WHERE rh.time BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
and CURRENT_DATE() /* ie to end of yesterday) */
AND s.description like '%unfixed'
GROUP BY r.id
ORDER BY r.year, r.request_number
[nycris_request_specimens]
SELECT
rs.request_id,
group_concat(s.sample_code) as 'code',
group_concat(s.description separator '; ') as 'description'
FROM request_specimen rs
join specimens s on (rs.specimen_id = s.id)
WHERE rs.request_id in (??)
GROUP BY request_id
[nycris_result_summaries]
SELECT
rs.request_id,
rs.results_summary,
ls.section_name
FROM request_result_summaries rs
join lab_sections ls on rs.lab_section_id = ls.id
WHERE rs.request_id in (??)
[get_email_contacts]
SELECT
id,
display_name,
`scope`,
referral_source_id,
GROUP_CONCAT(contact_address) as 'contacts'
FROM email_contacts
WHERE type = ?
and is_active = 'yes'
GROUP BY display_name, `scope`, referral_source_id
[mail_reports_request_ids]
SELECT r.id
FROM requests r
JOIN patient_case pc on (r.patient_case_id = pc.id)
JOIN referrer_department rd on (r.referrer_department_id = rd.id)
JOIN hospital_departments hd on (rd.hospital_department_code = hd.id)
JOIN referral_sources rs on (pc.referral_source_id = rs.id)
JOIN request_initial_screen ris on (ris.request_id = r.id)
JOIN screens s on (ris.screen_id = s.id)
JOIN request_report rr ON (r.id = rr.request_id)
JOIN status_options so ON (r.status_option_id = so.id)
LEFT JOIN ( request_trial rt JOIN clinical_trials ct on (rt.trial_id = ct.id) )
on (rt.request_id = r.id )
WHERE /* constraints added in script */
[pathlinks_samples]
SELECT
r.request_number,
( r.year - 2000 ),
rs.display_name,
DATE(rh.time),
GROUP_CONCAT(s.sample_code)
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 parent_organisations po on rs.parent_organisation_id = po.id
JOIN request_specimen rs2 on rs2.request_id = r.id
JOIN specimens s on rs2.specimen_id = s.id
JOIN request_history rh on ( rh.request_id = r.id and rh.action = 'authorised' )
WHERE s.description REGEXP 'trephine|block|fixed'
and YEARWEEK(rh.time, 1) = YEARWEEK(CURRENT_DATE(), 1)
and po.parent_code IN (??)
GROUP BY r.id
[requests_received]
SELECT r.id
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)
WHERE YEARWEEK(r.created_at, 1) = YEARWEEK(CURRENT_DATE(), 1)
AND p.first_name NOT IN ('lgi', 'pin')
AND rs.id IN (??)
[requests_authorised]
SELECT r.id
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_history rh on (rh.request_id = r.id and rh.action = 'authorised')
WHERE YEARWEEK(rh.time, 1) = YEARWEEK(CURRENT_DATE(), 1)
AND p.first_name NOT IN ('lgi', 'pin')
AND rs.id IN (??)
[requests_and_reports]
SELECT
UCASE(p.last_name) as last_name,
CONCAT(UPPER(SUBSTRING(p.first_name, 1, 1)), LOWER(SUBSTRING(p.first_name FROM 2)))
AS first_name,
p.dob,
p.nhs_number,
pc.unit_number,
rs.display_name as 'location',
r2.name as 'referrer',
hd.display_name as 'department',
DATE(r1.created_at) as 'registered',
DATE(rh.time) as 'authorised'
FROM requests r1
JOIN patient_case pc on (r1.patient_case_id = pc.id)
JOIN patients p on (pc.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 hospital_departments hd on (rd.hospital_department_code = hd.id)
JOIN referral_sources rs on (pc.referral_source_id = rs.id)
LEFT JOIN request_history rh on (rh.request_id = r1.id and rh.action = 'authorised')
WHERE r1.id IN (??)
ORDER BY p.last_name, p.first_name
