I got the table with pts with first medication_date by medication_type. I used modified code to get pts with first diagnosis_date by diagnosis_type
patient_num, first_med_date, med_type
patient_num, first_diagnosis_date, diagnosis_type
now I need to get a matrix of n-medication_types by m-diagnosis_types. Also, disease_type i needs to happen after first medication_type date to be counted in that particular cell. One patient can have more than disease_type and be on more than one medication_type
select m.med_type, d.diag_type, count(distinct c.pt_num) as unique_num_pts
from firstDiagnosisByTypePerPatient d, firstMedicationByTypePerPatient m
where d.patient_num = m.patient_num and d.start_date >= m.start_date
group by m.med_type, d.diagnosis_type
order by m.med_type, d.diagnosis_type
this works fine -I think!- but if there are no counts for a given diagnosis I don't get that row, and I'd like to get all rows/columns with counts and if there's no patients satisfying the criteria, then get 0 in that cell.
Not sure how to do that.