SQL queries for dcm4chee

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;