Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mavericky
Posting Yak Master
117 Posts |
 Posted - 2012-08-29 : 20:39:35
|
| 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-08-29 : 21:15:47
|
| You can use an aggregate function such as SUM or COUNT in the HAVING clause. |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-08-29 : 21:17:40
|
| SELECT Medical_record_number, count(distinct Diagnosis_location) FROM PatientData GROUP BY Medical_record_numberHAVING count(distinct Diagnosis_location) > 1; |
|
|
|
mavericky
Posting Yak Master
117 Posts |
Posted - 2012-08-29 : 21:45:13
|
| Thanks a lot jeffw8713 and sunitabeck!!! |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-08-30 : 01:59:13
|
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 PATIENTDATAGROUP BY Firstname, LASTNAME, DATE_OF_BIRTHHAVING COUNT(DISTINCT Diagnosis_location) >1; |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-08-30 : 14:47:46
|
| 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. |
|
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-08-31 : 04:18:25
|
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 |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-08-31 : 04:38:38
|
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[spoiler]Time is always against us[/spoiler] |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-08-31 : 12:54:25
|
| 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 |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2012-09-01 : 10:28:50
|
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. |
|
|
|
|
|
|
|
|
can anbody please solve this query?