[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2009-07-01
15
Medium Priority
?
294 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:garus
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
15 Comments
 
LVL 12

Expert Comment

by:kevin_u
ID: 24759931
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
 

Author Comment

by:garus
ID: 24759954
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
 

Author Comment

by:garus
ID: 24759967
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 12

Accepted Solution

by:
kevin_u earned 1500 total points
ID: 24760003
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
 

Author Comment

by:garus
ID: 24760010
oh, sorry. it is a typo. It should be distinct d.pt_num. I'll give this a try
0
 

Author Comment

by:garus
ID: 24760030
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
 
LVL 12

Expert Comment

by:kevin_u
ID: 24760048
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
 

Author Comment

by:garus
ID: 24760054
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
 

Author Closing Comment

by:garus
ID: 31599042
great help.
0
 
LVL 12

Expert Comment

by:kevin_u
ID: 24760070
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
 

Author Comment

by:garus
ID: 24760077
should it be left outer join or a full outer join?
0
 
LVL 12

Expert Comment

by:kevin_u
ID: 24760098
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
 

Author Comment

by:garus
ID: 24760115
yep. that's what I wanted.
0
 

Author Comment

by:garus
ID: 24760278
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
 

Author Comment

by:garus
ID: 24760355
never mind. I did some individual colum/row checks and it's fine.
Thanks so much!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question