select * from data_view dv where dv.patient_num; select dv.start_date, dv.code_label from data_view dv where dv.patient_num and dv.code like '%KUH|FLO_MEAS_ID+LINE:1226%' order by dv.start_date;
select distinct dv.code_label from data_view dv where dv.code like '%KUH|FLO_MEAS_ID+LINE:1226%';
/* we will need to get rid of "Asleep" and convert the code label to the proper pain score. */
selectpatient_num, dv.start_date, cast(dv.code_label as INTEGER) - 1 as pain_score from data_view dv where dv.patient_num and dv.code like '%KUH|FLO_MEAS_ID+LINE:1226%' and dv.code_label not like '12. Asleep' order by dv.patient_num, dv.start_date;
/*look at the whole set */
drop table pain_scores;
create table pain_scores as
selectpatient_num, dv.start_date as pain_score_dt, cast(dv.code_label as INTEGER) - 1 as pain_score
fromdata_view dv
wheredv.code like '%KUH|FLO_MEAS_ID+LINE:1226%' and dv.code_label not like '12. Asleep'
order by dv.patient_num, dv.start_date;
select * from pain_scores;
/*next, getting the fact of interest */
selectpatient_num, start_date, code, variable, code_label, end_date
fromdata_view dv
wheredv.patient_num and dv.code not like '%KUH|FLO_MEAS_ID%'
order by start_date;
/* Aqeel wants to know when the first documented diagnosis happened for any type of OA and for any type of DM */
selectpatient_num, min(start_date)
fromdata_view dv
wheredv.patient_num and dv.code not like '%KUH|FLO_MEAS_ID%' and dv.variable like '%osteoarthr%'
group by patient_num
order by start_date;
drop table first_oa_diagnosis;
create table first_oa_diagnosis as
selectpatient_num, min(start_date) as first_oa_dx
fromdata_view dv
wheredv.code not like '%KUH|FLO_MEAS_ID%' and dv.variable like '%arthr%'
group by patient_num
order by start_date;
select * from first_oa_diagnosis;
select ps.*, fod.first_oa_dx
frompain_scoresps, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num
order by ps.patient_num, ps.pain_score_dt;
select ps.*, fod.first_oa_dx
frompain_scoresps, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num and ps.pain_score_dtfirst_oa_dx
order by ps.patient_num, ps.pain_score_dt;
select distinct code
fromdata_view
wheredata_view.code like '%KUH|FLO_MEAS_ID%';
select patient_num, start_date, code, variable, code_label
fromdata_view dv
wheredv.code like '%KUH|FLO_MEAS_ID%' and dv.code_label not like '12. Asleep' and variable in ('23. Leg', '04. Back')
order by dv.patient_num, start_date ;
/* this shows the pain scores and the pain location together*/
select patient_num, start_date, code, variable, code_label
fromdata_view dv
wheredv.code like '%KUH|FLO_MEAS_ID%' and dv.code_label not like '12. Asleep' order by dv.patient_num, start_date ;
drop table pain_scores;
create table pain_scores as
selectpatient_num, dv.start_date as pain_score_dt, date (dv.start_date) as pain_day, cast(dv.code_label as INTEGER) - 1 as pain_score
fromdata_view dv
where dv.code like '%KUH|FLO_MEAS_ID+LINE:1226%' and dv.code_label not like '12. Asleep'
order by dv.patient_num, dv.start_date;
drop table pain_location;
create table pain_location as
selectpatient_num, dv.start_date as pain_location_dt, date (dv.start_date) as pain_location_day, dv.code_label as pain_location
fromdata_view dv
wheredv.code like '%KUH|FLO_MEAS_ID+LINE:301%'
order by dv.patient_num, dv.start_date;
select * from pain_scores;
select * from pain_location;
/* this gives me side by side pain score and location but with mutlpleobsevervations on the same time and day*/
select * from pain_scoresps, pain_locationpl
whereps.patient_num= pl.patient_num and ps.pain_day=pl.pain_location_day ;
/* we want to match the exact time of pain scores to the location */
select * from pain_scoresps, pain_locationpl
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt;
/* all of our locations are associated with OA :)*/
select distinct pl.pain_location
frompain_scoresps, pain_locationpl
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt;
selectavg(ps.pain_score)
frompain_scoresps, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num ;
selectavg (ps.pain_score), ps.patient_num, pain_score_dt
frompain_scoresps, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num
group by ps.pain_score_dt;
selectavg(ps.pain_score)
frompain_scoresps, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num and ps.pain_score_dtfirst_oa_dx
select (ps.pain_score), ps.patient_num
frompain_scoresps, pain_score_dt, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num and ps.pain_score_dtfirst_oa_dx
group by ps.patient_num
select (ps.pain_score), ps.patient_num
frompain_scoresps, first_oa_diagnosisfod
whereps.patient_num=fod.patient_num and ps.pain_score_dtfirst_oa_dx
group by ps.patient_num;
select distinct variable from data_view dv;
/*to average every single patient score (pain)*/
selectavg (ps.pain_score), COUNT (ps.pain_score), min (ps,pain_score), max(ps.pain_score), pain_location, ps.patient_num
frompain_scoresps, pain_locationpl, first_oa_diagnosisfod
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt group by ps.patient_num, pain_location;
/* to reorganize the dataset*/
selectavg (ps.pain_score), count (ps.pain_score), min (ps.pain_score), max (ps.pain_score), pain_location, ps.patient_num, pain_score_dt, first_oa_dx
frompain_scoresps, pain_locationpl, first_oa_diagnosisfod
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt and ps.pain_score_dt like first_oa_dx
group by ps.pain_score, ps.patient_num, pain_location, pain_score_dt;
selectavg (ps.pain_score), count (ps.pain_score), min (ps.pain_score), max (ps.pain_score), pain_location, count (distinct ps.patient_num)
frompain_scoresps, pain_locationpl
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt group by pain_location;
selectavg (ps.pain_score), count (ps.pain_score), min (ps.pain_score), max (ps.pain_score), pain_location, count (distinct ps.patient_num) as num_patients
frompain_scoresps, pain_locationpl
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt group by pain_location
order by num_patientsdesc;
/* age and sex*/
select * from patient_view ;
select count (patient_num), sex, avg (age) from patient_view group by sex;
/* join age and gender to pain table*/
selectavg (ps.pain_score), count (ps.pain_score), min (ps.pain_score), max (ps.pain_score), pain_location, count (distinct ps.patient_num) as num_patients, avg (pv.age)
frompain_scoresps, pain_locationpl, patient_viewpv
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt and ps.patient_num=pv.patient_num
group by pain_location
order by num_patientsdesc;
/* group the patient by gender by pain table*/
selectavg (ps.pain_score), count (ps.pain_score), min (ps.pain_score), max (ps.pain_score), pain_location, count (distinct ps.patient_num) as num_patients
frompain_scoresps, pain_locationpl
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt group by pain_location
order by num_patientsdesc;
/* looking at just pain scores and demographic attributes (percent of patients among all set ordered by sex) */
selectavg (ps.pain_score), count (ps.pain_score), min (ps.pain_score), max (ps.pain_score), (count (distinct ps.patient_num)/8.70) as percentofpatients, count (distinct ps.patient_num) as num_patients, avg (pv.age), pain_location, sex
frompain_scoresps, pain_locationpl, patient_viewpv
whereps.patient_num= pl.patient_num and ps.pain_score_dt=pl.pain_location_dt and ps.patient_num=pv.patient_num
group by pain_location, sex
order by percentofpatientsdesc;