generate matrix with counts from uniquepts on each medication who also had a diagnosis type

Hi,
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
tables are:
medications:
patient_num, first_med_date, med_type

diagnoses:
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

I tried:

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.
Thanks
garusAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kevin_uCommented:
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

There is an asterisk before the equals....
I don't have your tables and table c for c.pt_num isn't in your query, but it appears you want an outer join using the old syntax.

*= should show all the d's even if there are no m's.
0
garusAuthor Commented:
Thanks! by I get this error:
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

can you help rewriting the query? I'll give it a try, but your help is very much appreciated
0
garusAuthor Commented:
I tried this but I get an error on the join. What's wrong?

select m.med_type, d.diag_type, count(distinct c.pt_num) as unique_num_pts
from firstDiagnosisByTypePerPatient d outer join firstMedicationByTypePerPatient m
on  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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

kevin_uCommented:
select m.med_type, d.diag_type, count(distinct c.pt_num) as unique_num_pts
from firstDiagnosisByTypePerPatient d outer join firstMedicationByTypePerPatient m
on  d.patient_num = m.patient_num
where d.start_date >= m.start_date
group by m.med_type, d.diagnosis_type
order by m.med_type, d.diagnosis_type

that should solve the syntax problem, but c.pt_num is still missing.

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garusAuthor Commented:
oh, sorry. it is a typo. It should be distinct d.pt_num. I'll give this a try
0
garusAuthor Commented:
nope. I only get those cells where the count is not zero. Also, I changed the 'outer join' to 'full outer join' because I was getting an error
0
kevin_uCommented:
The problem is d.start_date >= m.start_date
because when no m records are there, m.start_date is null
I don't think this will work, but you can try it:

where d.start_date >= m.start_date or m.start_date is null

I think that fails because its too early in the join process.

Beyond that I'd have to set up a test bed and experiment a little.


0
garusAuthor Commented:
m.start_date is never null. Thanks.  I'll accept your solution as partial, and give you the points -You helped me a lot.
Thanks!
0
garusAuthor Commented:
great help.
0
kevin_uCommented:
Thanks, if you get stuck, I'll make a test bed for it and go farther.

m.start_date is null is what is returned when an outer join returns data for a related row with no joined row.... I worded that poorly hopefully you get my meaning.

Try the join without d.start_date >= m.start_date, you'll see what I mean... and you'll almost have the result you wanted.
0
garusAuthor Commented:
should it be left outer join or a full outer join?
0
kevin_uCommented:
my understanding of your orignal question tells me left outer join ... or simply left join.

This page might help...
http://en.wikipedia.org/wiki/Join_(SQL)
scroll down to left outer join.
0
garusAuthor Commented:
yep. that's what I wanted.
0
garusAuthor Commented:
wouldn't this give me the totals by diagnosis? The numbers don't match when I compare them with the results of the above query.

select d.diag_type, count(distinct d.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  d.diag_type
0
garusAuthor Commented:
never mind. I did some individual colum/row checks and it's fine.
Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.