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
 Help please with simple query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ianbauer
Starting Member

2 Posts

Posted - 10/09/2012 :  16:25:44  Show Profile  Reply with Quote
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

USA
1974 Posts

Posted - 10/09/2012 :  19:13:19  Show Profile  Visit chadmat's Homepage  Reply with Quote
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 - 10/09/2012 :  19:56:04  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 10/09/2012 :  21:25:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/10/2012 :  00:34:56  Show Profile  Reply with Quote
Check this link... Same query


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

--
Chandu
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.08 seconds. Powered By: Snitz Forums 2000