[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)
[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)
[diagnosis_status]
SELECT COUNT(*) as f,
if ( status = 'default', 'follow-up', status )
FROM request_report
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 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 t1
join diagnoses t2 on (t1.diagnosis_id = t2.id)
WHERE %CONSTRAINT%
GROUP BY t2.id
ORDER BY f desc
LIMIT 20