SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 can anbody please solve this query?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mavericky
Posting Yak Master

115 Posts

Posted - 08/29/2012 :  20:39:35  Show Profile  Reply with Quote
It would be a great help!!
Thanks in anticipation!!

create table patientdata (
Firstname varchar(50),
Lastname varchar(50),
Date_of_birth datetime,
Medical_record_number varchar(20),
Diagnosis_location varchar(20),
Diagnosis_date datetime,
Diagnosis_code varchar(20)
);

The data in the flat file looks like this:

First Last date_of_birth Med Rec No Diag Loc Diag Date Diag Code
-------------------------------------------------------------------------------------
'jane', 'jones', '2/2/2001', 'MRN-11111', 'Mayo Clinic', '3/3/2009', 'diabetes'

'jane', 'jones', '2/2/2001', 'MRN-11111', 'Mayo Clinic', '1/3/2009', 'asthma'

'jane', 'jones', '5/5/1975', 'MRN-88888', 'Lahey Clinic', '2/17/2009', 'flu'

'tom', 'smith', '4/12/2002', 'MRN-22222', 'Lahey Clinic', '3/3/2009', 'diabetes'

'tom', 'smith', '4/12/2002', 'MRN-33333', 'Mayo Clinic', '1/3/2009', 'asthma'

'tom', 'smith', '4/12/2002', 'MRN-33333', 'Mayo Clinic', '2/7/2009', 'asthma'

'jack', 'thomas', '8/10/1991', 'MRN-44444', 'Mayo Clinic', '3/7/2009', 'asthma'

'jack', 'thomas', '8/10/1991', 'MRN-44444', 'Lahey Clinic', '3/16/2009', 'flu'
You can assume that no two patients have the same firstname, lastname, and date of birth combination.

I want to Write a query that shows which of these patients were diagnosed in more than one location.

This problem has many solutions, but I want to be able to find one that uses a single query with no subqueries.


Thanks,
Amol

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 08/29/2012 :  21:15:47  Show Profile  Reply with Quote
You can use an aggregate function such as SUM or COUNT in the HAVING clause.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
768 Posts

Posted - 08/29/2012 :  21:17:40  Show Profile  Reply with Quote
SELECT Medical_record_number, count(distinct Diagnosis_location)
FROM PatientData
GROUP BY Medical_record_number
HAVING count(distinct Diagnosis_location) > 1;
Go to Top of Page

mavericky
Posting Yak Master

115 Posts

Posted - 08/29/2012 :  21:45:13  Show Profile  Reply with Quote
Thanks a lot jeffw8713 and sunitabeck!!!
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 08/30/2012 :  01:59:13  Show Profile  Reply with Quote
hi, u r missing tom-smith record (which is also satisfying ur criteria) with above query...

check once with following query:
SELECT Firstname, LASTNAME, DATE_OF_BIRTH, COUNT(DISTINCT Diagnosis_location)
FROM PATIENTDATA
GROUP BY Firstname, LASTNAME, DATE_OF_BIRTH
HAVING COUNT(DISTINCT Diagnosis_location) >1;
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
768 Posts

Posted - 08/30/2012 :  14:47:46  Show Profile  Reply with Quote
bandi - how do you figure there is anything missing? The MRN value will be unique per patient - at least it should be and by grouping on that value you will get each individual patient even if the lastname, firstname and date of birth are the same.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 08/31/2012 :  04:18:25  Show Profile  Reply with Quote
hi williams,
Thanks for your reply.
In the sample data, there is one patient tom-smith. He has taken treatment in two locations(mayo clinic and lahey clinic).
As per question they want to show patients diagnosed in more than one location. Thats why i thought like that. Revert me back
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17430 Posts

Posted - 08/31/2012 :  04:38:38  Show Profile  Reply with Quote
But how do you know tom-smith of MRN-22222 and tom-smith of MRN-33333 are the same tom-smith ?

Shouldn't you have some sort of a National identification number in the table that can identify it is the same patient ?


KH
Time is always against us


Edited by - khtan on 08/31/2012 04:40:57
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2143 Posts

Posted - 08/31/2012 :  12:54:25  Show Profile  Reply with Quote
To quote from the specs given by the OP
"You can assume that no two patients have the same firstname, lastname, and date of birth combination."








How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
768 Posts

Posted - 09/01/2012 :  10:28:50  Show Profile  Reply with Quote
quote:
Originally posted by DonAtWork

To quote from the specs given by the OP
"You can assume that no two patients have the same firstname, lastname, and date of birth combination."



And in his example data - he has 2 different patients with the same firstname, lastname and date of birth shown by different MRN numbers.

In healthcare - the MRN number is (or should be) unique per patient. If Tom Smith is the same person - he would not have 2 different MRN values. If they are - in fact - the same person then there is a process in place to merge those patient records and inactivate the non-surviving patient record.

This is standard functionality in all of the healthcare systems I have worked on.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000