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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help please with simple query

Author  Topic 

ianbauer
Starting Member

2 Posts

Posted - 2012-10-09 : 16:25:44
hello, I have been asked to answer this question and have no idea. can someone help me? I am told it is easy.
Assume we have loaded a flat file with patient diagnosis data into a table called “patientdata”. The table structure is:

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.

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

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.

chadmat
The Chadinator

1974 Posts

Posted - 2012-10-09 : 19:13:19
Homework?

What do you have so far? Where are you running into problems?

-Chad
Go to Top of Page

ianbauer
Starting Member

2 Posts

Posted - 2012-10-09 : 19:56:04
Hi
No not homework. Im trying for a job and they threw this question in on their online submission form. I have not used sql..yes Im old..50,,,and Im just plain in the weeds. i've gotten an online course and Im trying to wade through in the hopes that I can at least understand what is being asked of me.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-09 : 21:25:32
look up for GROUP BY and COUNT in google and make a try. If you face any issues post and we will help you out.

Otherwise you wont be learning to do it yourself

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-10 : 00:34:56
Check this link... Same query


http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=178342

--
Chandu
Go to Top of Page
   

- Advertisement -