dcm4chee query study availability

This query can be useful to list in a range of time the sudies wich availability is ONLINE (0= online, 1= nearline).

SELECT distinct(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
where study.study_datetime >= '2000-01-01' and study.study_datetime <= '2010-01-31'
and study.availability = 0 ;

Another sql query for dcm4chee

Find study instance uid for all the files stored before a date.

SELECT distinct(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 files.instance_fk = instance.pk
where files.created_time < '2012-12-15';

Another SQL query for dcm4chee

Like a previous one, useful to discover files with full path owned by a study.
SIUID = study instance uid

SELECT concat(filesystem.dirpath,'/', files.filepath)
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
JOIN filesystem ON files.filesystem_fk = filesystem.pk
WHERE study.study_iuid = '$SIUID';

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;