How many US patients have not birth date
select count(distinct s.patient_fk)
from study as s
join patient as p
where s.patient_fk = p.pk
and s.mods_in_study = 'US'
and ( p.pat_birthdate not like '1%'
and p.pat_birthdate not like '2%'
or p.pat_birthdate is NULL )
How many US patients have not M or F sex?
select count(distinct s.patient_fk)
from study as s
join patient as p
where s.patient_fk = p.pk
and s.mods_in_study = 'US'
and (p.pat_sex is NULL
or p.pat_sex = 'O' )
How many studies for each modality?
select distinct mods_in_study , count(mods_in_study) as conto from study group by mods_in_study
How many patients without studies?
SELECT patient.pat_name, patient.pat_id, patient.pk as papk, study.pk as stpk, study.num_series, study.num_instances
FROM patient LEFT JOIN study ON (patient.pk = study.patient_fk)
WHERE (study.pk IS NULL)
ORDER BY patient.pat_name ASC, patient.pk ASC, study.study_datetime ASC
Studies with a number of instances > 10000 Grouped by Year
select count(cnt) as num, year
from(
SELECT COUNT(*) as cnt,
YEAR(study_datetime) as year
FROM instance as i
join series as s
on i.series_fk = s.pk
join study as stu
on stu.pk = s.study_fk
GROUP BY YEAR(study_datetime), i.series_fk
HAVING COUNT(*) > 10000) XX
GROUP BY year
Files patient and study iuid year and month
SELECT files.filepath, patient.pat_name, study.study_iuid
FROM series
join study on series.study_fk = study.pk
join patient on study.patient_fk = patient.pk
join instance on instance.series_fk = series.pk
join files on instance.pk = files.instance_fk
WHERE series.src_aet="DCM4STORICOMS" and YEAR(study.study_datetime) = '2011'
and MONTH(study.study_datetime) = 7;