def procedures_gender_count(selection=None):
sql_query = """SELECT c_name,count(distinct patient_num) as anzahl,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode AND patient_num in (SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd='M')) as anzahl_M,(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode AND patient_num in (SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd='F')) as anzahl_F
FROM i2b2metadata.i2b2
join i2b2demodata.observation_fact on observation_fact.concept_cd = c_basecode
WHERE c_fullname LIKE '\\i2b2\\Procedures%'
group by c_name,c_basecode
order by anzahl desc limit 10;"""
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT c_name,count(distinct patient_num) as anzahl,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode AND patient_num in
((SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd='M')
INTERSECT ({})))as anzahl_M,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode AND patient_num in
((SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd='F')
INTERSECT ({})))as anzahl_F
FROM i2b2metadata.i2b2
join i2b2demodata.observation_fact on observation_fact.concept_cd = c_basecode
WHERE c_fullname LIKE '\\i2b2\\Procedures%' and
patient_num in ({})
group by c_name,c_basecode
order by anzahl desc limit 10;""".format(pattern, pattern, pattern)
return sql_query
def all_patient(selection=None):
sql_query = """SELECT COUNT(patient_num) FROM i2b2demodata.patient_dimension;"""
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE patient_num in ( {} )""".format(pattern)
return sql_query
def gender_equal_male(selection=None):
sql_query = """SELECT COUNT(patient_num) FROM i2b2demodata.patient_dimension WHERE sex_cd = 'M';"""
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT count(DISTINCT obs.patient_num) FROM observation_fact obs
WHERE patient_num in ((SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd = 'M')
INTERSECT ({}))""".format(pattern)
return sql_query
def max_stay_of_days(selection=None):
sql_query = """SELECT max(length_of_stay) from i2b2demodata.visit_dimension"""
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT max(length_of_stay) from i2b2demodata.visit_dimension
WHERE patient_num in ( {} )""".format(pattern)
return sql_query
def age_distribution(begin, end, gender, selection=None):
sql_query = """SELECT COUNT(patient_num) FROM i2b2demodata.patient_dimension
WHERE age_in_years_num between {} AND {} AND sex_cd = '{}' """.format(begin, end, gender)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT count(DISTINCT obs.patient_num) FROM observation_fact obs
WHERE patient_num in (( SELECT patient_num from i2b2demodata.patient_dimension WHERE
sex_cd = '{}' AND age_in_years_num between {} AND {}) INTERSECT ({}))""".format(
gender, begin, end, pattern)
return sql_query
def stay_of_day(days, selection=None):
sql_query = """SELECT count(DISTINCT patient_num) from i2b2demodata.observation_fact where
patient_num in (SELECT patient_num from i2b2demodata.visit_dimension
where length_of_stay = {});""".format(days)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE patient_num in ((SELECT patient_num from i2b2demodata.visit_dimension
where length_of_stay = {})
INTERSECT ({}))""".format(days, pattern)
return sql_query
def labory_by_flag(value, sex, selection=None):
sql_query = """SELECT nval_num from i2b2demodata.observation_fact where nval_num notnull and concept_cd
in (SELECT concept_cd from i2b2demodata.concept_dimension
where concept_path LIKE '{}%')
AND patient_num in (SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd='{}')""".format(value, sex)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT nval_num from i2b2demodata.observation_fact where nval_num notnull and concept_cd
in (SELECT concept_cd from i2b2demodata.concept_dimension
where concept_path LIKE '{}%')
AND patient_num in ((SELECT patient_num from i2b2demodata.patient_dimension WHERE sex_cd='{}')
INTERSECT ({}))""".format(value, sex, pattern)
return sql_query
def vital_status(state, gender, selection=None):
sql_query = """SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE patient_num in (SELECT patient_num from observation_fact WHERE concept_cd in
(SELECT concept_cd from i2b2demodata.concept_dimension WHERE concept_path like '\\i2b2\\Demographics\\Vital Status\\{}%')
INTERSECT (SELECT patient_num from i2b2demodata.patient_dimension where sex_cd='{}'))""".format(state, gender)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE patient_num in (SELECT patient_num from observation_fact WHERE concept_cd in
(SELECT concept_cd from i2b2demodata.concept_dimension WHERE concept_path like '\\i2b2\\Demographics\\Vital Status\\{}%')
INTERSECT (SELECT patient_num from i2b2demodata.patient_dimension where sex_cd='{}')
INTERSECT ({}))""".format(state, gender, pattern)
return sql_query
def tree_patient_count(table_name, selection=None):
sql_query = """SELECT DISTINCT c_hlevel, c_name,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd in (SELECT concept_cd FROM i2b2demodata.concept_dimension WHERE concept_path = c_fullname)),
c_fullname FROM i2b2metadata.{}
order by c_fullname;""".format(table_name)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT DISTINCT c_hlevel, c_name,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd in (SELECT concept_cd FROM i2b2demodata.concept_dimension WHERE concept_path = c_fullname)
AND patient_num in ( {} ) ),
c_facttablecolumn, c_tablename,c_columnname,c_operator, c_fullname FROM i2b2metadata.{}
order by c_fullname;""".format(pattern, table_name)
return sql_query
def tree_patient_all_over_0(table_name, selection=None):
sql_query = """SELECT DISTINCT c_hlevel, c_name,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode),
c_facttablecolumn, c_tablename,c_columnname,c_operator, c_fullname,c_basecode FROM i2b2metadata.icd10_icd9
WHERE c_hlevel > 0
order by c_fullname;""".format(table_name)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT DISTINCT c_hlevel, c_name,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode and patient_num in ({})),
c_facttablecolumn, c_tablename,c_columnname,c_operator, c_fullname,c_basecode FROM i2b2metadata.icd10_icd9
WHERE c_hlevel > 0
order by c_fullname;""".format(pattern, table_name)
return sql_query
def tree_patient_count_first_hierachielvl(table_name, like, selection=None):
sql_query = """SELECT DISTINCT c_hlevel, c_name,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode),
c_facttablecolumn, c_tablename,c_columnname,c_operator, c_fullname,c_basecode FROM i2b2metadata.{}
WHERE c_fullname LIKE '{}%'
order by c_fullname;""".format(table_name, like)
if selection is not None:
pattern = selection_patient_count(selection)
if pattern != "":
sql_query = """SELECT DISTINCT c_hlevel, c_name,
(SELECT count(DISTINCT patient_num) FROM i2b2demodata.observation_fact
WHERE concept_cd = c_basecode
AND patient_num in ( {} ) ),
c_facttablecolumn, c_tablename,c_columnname,c_operator, c_fullname,c_basecode FROM i2b2metadata.{}
WHERE c_fullname LIKE '{}%'
order by c_fullname;""".format(pattern, table_name, like)
return sql_query