Solved

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

Posted on 2009-07-01
15
286 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
  • 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
 
LVL 12

Accepted Solution

by:
kevin_u earned 500 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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now