[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]
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 = 'new'
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)
[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 r.*
FROM request_report r
join diagnoses d on r.diagnosis_id = d.id
WHERE r.updated_at = DATE_SUB(CURDATE(), INTERVAL 1 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 6 MONTH)
AND active = 'yes'
[lab_param_monitoring_data]
SELECT
DATE_FORMAT(r.`created_at`, '%Y-%m-%d') 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` = ?
ORDER BY r.id
[unknown_referrers]
SELECT
req.`request_number`,
req.`year`,
req.`created_at`,
rs.`display_name`
FROM
`referrer_department` rd
JOIN `requests` req ON req.`referrer_department_id` = rd.`id`
JOIN `referrers` ref ON rd.`referrer_id` = ref.`id`
JOIN `patient_case` pc on req.`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` = req.`id`
WHERE ref.`national_code` like '%999998'
and req.`created_at` >= DATE_SUB(CURRENT_DATE(), INTERVAL ? DAY)
and rt.`request_id` is null
[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`,
DATE_FORMAT(req.`created_at`, '%Y') - DATE_FORMAT(p.`dob`, '%Y')
- (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`,
IF(rt.`request_id`,'ST','01') as 'category',
s2.`description` as '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`
JOIN `request_initial_screen` ris on ris.`request_id` = req.`id`
JOIN `screens` s2 on ris.`screen_id` = s2.`id`
LEFT JOIN `request_trial` rt 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`
[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)
)
[referrer_department_map]
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)
[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 req.`created_at` >= 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_FORMAT(rh.`time`, '%Y-%m-%d'),
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_FORMAT(rh.`time`, '%Y-%m-%d'),
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`
[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_FORMAT(p.`created_at`, '%Y-%m-%d'),
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`
WHERE p.`created_at` BETWEEN DATE_SUB(CURDATE(), INTERVAL ? DAY) and CURDATE()
and p.`nhs_number` IS NULL
and p.`first_name` <> 'LGI'
and po.`parent_code` = 'RR8'
[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_FORMAT(req.`created_at`, '%Y-%m-%d'),
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_FORMAT(req.`created_at`, '%Y-%m-%d') as 'registered',
DATE_FORMAT(rh.`time`, '%Y-%m-%d') 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_FORMAT(req.`created_at`, '%Y-%m-%d') 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`
[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`
[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.`id`
