Hi, Here's the queries I ran at UW-Madison and some sample output. The sample output is for example only and does not reflect reality. If you haven't run the queries yet, for Q5, if you could reverse the ordering of number of occurrences and score range that would be great because then all the queries have the count, then the text. If not, no worries, we will look at the data and switch the results when we enter then into the database.
---- Debbie -- Debbie Yoshihara Informatics Specialist SMPH-IT, Biomedical Informatics Services School of Medicine and Public Health, UW-Madison dlyos...@wisc.edu Tel:(608)263-5643
/* Q1 Population statistics */
select 'WISC', 'Q1', count (distinct patient_num) from patient_dimension;
/* Q2 GPC Active patients */
select 'WISC', 'Q2', count (distinct patient_num) from gpc_active_patients;
/* Keep GPC active patients in a table */
/* # patients w/at least 2+ visits (separated by 30+ days) in the last
3 years */
create table gpc_active_patients
( patient_num bigint );
insert into gpc_active_patients
select x.patient_num
from (
select y.patient_num
, count(y.encounter_num) as encounter_num_count
from (
select z.patient_num as patient_num
, z.encounter_num as encounter_num
, z.start_date as start_date
, lag(z.start_date) over (order by z.patient_num,
z.encounter_num) as lag_date
from observation_fact z
where z.start_date > '2011-10-01: 00:00:00'
) y
where y.start_date > y.lag_date + 30
group by y.patient_num
) x
where x.encounter_num_count > 1
/* Always used GPC Active Patients from now on */
/* Q3 Demographics - Sex */
select 'WISC','Q3', count(distinct b.patient_num), a.sex_cd
from patient_dimension a,
gpc_active_patients b
where b.patient_num = a.patient_num
group by a.sex_cd;
/* Q4 Demographics - Ethnicity */
select 'WISC', 'Q4', count(distinct b.patient_num), concept_cd
from observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num and
concept_cd like 'ETHN%'
group by concept_cd;
/* Q5 Demographics - Age ranges switch number_of_occurences */
/* and score_range so all counts come in second */
select 'WISC', 'Q5', count(*) as number_of_occurences, t.myrange as score_range
from (
select case
when age_in_years_num between 0 and 9 then '0- 9'
when age_in_years_num between 10 and 19 then '10-19'
when age_in_years_num between 20 and 29 then '20-29'
when age_in_years_num between 30 and 39 then '30-39'
when age_in_years_num between 40 and 49 then '40-49'
when age_in_years_num between 50 and 59 then '50-59'
when age_in_years_num between 60 and 69 then '60-69'
when age_in_years_num between 70 and 79 then '70-79'
when age_in_years_num between 80 and 130 then '80+'
else 'unknown' end as myrange
from patient_dimension a,
gpc_active_patients b
where b.patient_num = a.patient_num) t
group by t.myrange
order by score_range;
/* Q6 Demographics - Religion */
select 'WISC','Q6', count(distinct b.patient_num), a.religion_cd
from patient_dimension a,
gpc_active_patients b
where b.patient_num = a.patient_num
group by religion_cd
order by religion_cd;
/* Q7 Demographics - Gender */
/* Wisconsin does not have a separate category for gender vs sex */
/* Query not done */
/* Q8 Demographics - Race */
select 'WISC','Q8', count(distinct b.patient_num), a.race_cd
from patient_dimension a,
gpc_active_patients b
where b.patient_num = a.patient_num
group by race_cd
order by race_cd;
/* Q9 Demographics - location, ZIP */
select 'WISC', 'Q9', count(distinct b.patient_num), a.zip_cd from
patient_dimension a,
gpc_active_patients b
where b.patient_num = a.patient_num
group by zip_cd
order by zip_cd;
/* Q10 Encounters with CPT */
select 'WISC','Q10', count(distinct encounter_num) from
observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num and
concept_cd like '%CPT%';
/* Q11 Encounter with provider */
select 'WISC','Q11', count(distinct encounter_num) from
observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num and
provider_id is not null and provider_id <> '@';
/* Q12 Encounters with a facility */
select 'WISC','Q12', count(distinct encounter_num)
from observation_fact a,
gpc_active_patients b,
PROVIDER_DIMENSION d
where b.patient_num = a.patient_num and
a.provider_id = d.provider_id and
d.provider_path like '%provider_facility%';
/* Q13 Encounters - inpatient and outpatient */
select 'WISC','Q13', count(distinct d.encounter_num), v.inout_cd
from observation_fact d,
gpc_active_patients e,
visit_dimension v
where e.patient_num = d.patient_num and
v.encounter_num = d.encounter_num
group by v.inout_cd;
/* Q14 Charge CPTS out of range */
/* NOT DONE */
/* Q15 Diagnosis out of range - ICD09 */
/* NOT DONE */
/* Q16 Diagnosis Valid ICD09 */
select 'WISC','Q16', count(distinct encounter_num) from
observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num and
concept_cd like '%ICD09%';
/* Q17 Encounters with no ICD09 */
select 'WISC','Q17', count(distinct encounter_num) from
observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num
minus
select 'WISC','Q17', count(distinct encounter_num) from
observation_fact c,
gpc_active_patients d
where d.patient_num = c.patient_num and
concept_cd like '%ICD09%';
/* Q18 Encounters with LOINC lab */
select 'WISC','Q18', count(distinct encounter_num) from
observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num
and concept_cd like '%LOINC%';
/* Q19 Encounters with non-LOINC lab */
select 'WISC','Q19', count(distinct encounter_num) from
observation_fact a,
gpc_active_patients b
where b.patient_num = a.patient_num
and concept_cd like '%LAB%';
/* Q20 How many providers (WISC does not have npi data) */
select 'WISC','Q20', count(distinct provider_id) from
provider_dimension;
GPC_QA SAMPLE.xlsx
Description: MS-Excel 2007 spreadsheet
_______________________________________________ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev
