[RESOLVED] Help with SQL Query

[GaryMazzone]

Hi guys I need some help on this one I'm not sure

SQL Problem using SQL Server 2000

Assume we have loaded a flat file with patient diagnosis data into a table called “Data”. The table structure is:

Create table Data (
Firstname varchar(50),
Lastname varchar(50),
Date_of_birth datetime,
Medical_record_number varchar(20),
Diagnosis_date datetime,
Diagnosis_code varchar(20))


The data in the flat file looks like this:

'jane','jones','2/2/2001','MRN-11111','3/3/2009','diabetes'
'jane','jones','2/2/2001','MRN-11111','1/3/2009','asthma'
'jane','jones','5/5/1975','MRN-88888','2/17/2009','flu'
'tom','smith','4/12/2002','MRN-22222','3/3/2009','diabetes'
'tom','smith','4/12/2002','MRN-33333','1/3/2009','asthma'
'tom','smith','4/12/2002','MRN-33333','2/7/2009','asthma'
'jack','thomas','8/10/1991','MRN-44444','3/7/2009','asthma'




You can assume that no two patients have the same firstname, lastname, and date of birth combination.

The problem is this: Tom Smith has 2 different medical record numbers. Write a query that would always show all the patients who are like Tom Smith – patients with more than one medical record number.

This problem has many solutions, but if you know SQL, you should be able to find one that uses a single query with no subqueries.


Gary

[TysonLPrice]

You mean just patients with more than one of the same code? Not tested:

Firstname,Lastname,Date_of_birth,Medical_record_number
from data
group by Firstname,Lastname,Date_of_birth,Medical_record_number
having count(Medical_record_number) > 1

[GaryMazzone]

No the same person with 2 different codes.

So the only real result should be for Tom Smith since he has code MRN-22222 and MRN-33333

[stlaural]

I think that would work, just need to remove Medical_record_number from the Group By Clause as we can identify unique patient by their name and Birth date.
And you can't select the Medial_record_number as its not included anymore.
Edit : Oh and you need the Distinct because some patients have more than 1 record with the same Medical_record_number.

Select Firstname,Lastname,Date_of_birth
from data
group by Firstname,Lastname,Date_of_birth
having count(Distinct(Medical_record_number)) > 1

[si_the_geek]

Perhaps this modification would work:

SELECT Firstname, Lastname, Date_of_birth, Count(Medical_record_number)
FROM Data
GROUP BY Firstname, Lastname, Date_of_birth
HAVING Count(Medical_record_number) > 1

[GaryMazzone]

Nope gives Jane Jones and Tom smith should only be tom smith

[GaryMazzone]

This is what I was using:

Select 
    Firstname, 
    LastName,
    Date_of_birth ,
    Medical_record_number 
From [Data] 
Group BY 
    Firstname, 
    LastName,
    Date_of_birth ,
    Medical_record_number

I get
Firstname LastName Date_of_birth Medical_record_number
jack thomas 1991-08-10 00:00:00.000 MRN-44444
jane jones 1975-05-05 00:00:00.000 MRN-88888
jane jones 2001-02-02 00:00:00.000 MRN-11111
tom smith 2002-04-12 00:00:00.000 MRN-22222
tom smith 2002-04-12 00:00:00.000 MRN-33333

[stlaural]

I just tested mine with very simple values

Firstname	Lastname	Date_of_birth                    Medical_record_number	  Diagnosis_date	                Diagnosis_code
a              b               2011-01-01 00:00:00.000      1                              2011-01-02 00:00:00.000    1
a              b               2011-01-01 00:00:00.000      2                              2011-01-03 00:00:00.000    2
a              b               2011-01-01 00:00:00.000      2                              2011-01-04 00:00:00.000    5
c              d               2011-01-02 00:00:00.000      3                              2011-01-03 00:00:00.000    1
a              b               2011-01-03 00:00:00.000      4                              2011-01-05 00:00:00.000    2
c              d               2011-01-02 00:00:00.000      3                              2011-01-06 00:00:00.000    3

As you can see only ab as 2 different records and its the only result I get with my query so it seems to work fine.

[GaryMazzone]

I'm confused What is the query you used? I see results that show for all how do you show only a,b as two different MRN?

[stlaural]

I posted my query right before si_the_geek's post. It's almost the same as TysonLPrice's.

Select Firstname,Lastname,Date_of_birth
from data
group by Firstname,Lastname,Date_of_birth
having count(Distinct(Medical_record_number)) > 1

[GaryMazzone]

OK got it thanks

[stlaural]

OK got it thanks

You're welcome!

[leinad31]

Is this just a theoretical exercise?

It all depends on how "clean" the data is. In our case (non-life insurance industry) the only practical approach is to clean the data a bit (remove excess space and non alpha characters, replace some tokens such as INCORPORATED with INC.) then generate a list in alphabetical order to be further processed manually.

You guys are better off since American names are generally simpler, e.g. (a) no two first names which results in client sometimes giving both and at other times just the first one, (b) no concatenation of maiden and married surnames, etc, etc.