[specimens_by_year]
SELECT COUNT(*) as f, r.year
FROM request_specimen rs
JOIN requests r on rs.request_id = r.id
GROUP BY r.year
ORDER BY r.year
[requests_by_year]
SELECT COUNT(*) as f, year
FROM requests
GROUP BY year
ORDER BY year
[requests_by_month]
SELECT COUNT(*) as f,
CONCAT(year, '_', DATE_FORMAT(created_at,'%b'))
FROM requests
WHERE %CONSTRAINT%
GROUP BY year,month(created_at)
ORDER BY year,month(created_at)
[diagnosis_errors]
SELECT COUNT(*) as f,
CONCAT(year, '_', DATE_FORMAT(created_at,'%b'))
FROM
requests r
JOIN request_diagnosis_history dh ON (dh.request_id = r.id)
JOIN diagnosis_change_options co ON (dh.option_id = co.id)
WHERE co.option_name LIKE 'error%'
AND %CONSTRAINT%
GROUP BY year,month(created_at)
ORDER BY year,month(created_at)
[requests_by_day_of_week]
SELECT COUNT(*) as f,
DAYNAME(created_at)
FROM requests
WHERE %CONSTRAINT%
GROUP BY DAYNAME(created_at)
ORDER BY DAYOFWEEK(created_at)
[reports_by_day_of_week]
SELECT
DAYNAME(created_at),
CASE
WHEN s.sample_code REGEXP '^(BMA|TB)' THEN 'BMA(T)'
WHEN s.sample_code REGEXP '[DGLRX][BL|F|SL|U|A]' THEN 'tissue biopsy'
ELSE 'others'
END as 'sample_type',
COUNT(*) as f
FROM request_report_view rr
JOIN ( request_specimen rs JOIN specimens s on(rs.specimen_id = s.id ) )
on (rs.request_id = rr.request_id)
WHERE %CONSTRAINT%
GROUP BY DAYNAME(created_at), sample_type
ORDER BY DAYOFWEEK(created_at), sample_type
[diagnosis_status]
SELECT COUNT(*) as f,
IF ( status = 'default', 'follow-up', status )
FROM request_report_view
WHERE %CONSTRAINT%
GROUP BY status
ORDER BY f desc
[presentation_frequency]
SELECT count(*) as f,
s.description
FROM requests r
join request_initial_screen ris on (ris.request_id = r.id)
join screens s on (ris.screen_id = s.id)
WHERE %CONSTRAINT%
GROUP BY s.id
ORDER BY f desc
LIMIT 20
[specimen_frequency]
SELECT count(*) as f,
s.description
FROM requests r
join request_specimen rs on (rs.request_id = r.id)
join specimens s on (rs.specimen_id = s.id)
WHERE %CONSTRAINT%
GROUP BY s.id
ORDER BY f desc
LIMIT 20
[new_diagnosis_frequency]
SELECT count(*) as f,
t2.name
FROM request_report_view t1
join diagnoses t2 on (t1.diagnosis_id = t2.id)
WHERE icdo3 is not null
and %CONSTRAINT%
and status = 'new'
GROUP BY t2.id
ORDER BY f desc
LIMIT 20
[diagnosis_frequency]
SELECT count(*) as f,
t2.name
FROM request_report_view t1
join diagnoses t2 on (t1.diagnosis_id = t2.id)
WHERE %CONSTRAINT%
GROUP BY t2.id
ORDER BY f desc
LIMIT 20
