[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