March 30, 2009 at 8:12 pm
Hi guys I have to do a report on the patient's data which is in two different tables.
Here are the sample tables:
Table 1
Doctor_ID Patient_ID Blood_Tests
123 1A 4
123 1B 67
222 6A 4
333 9G 756
.
.
.
Table 2
Doctor_ID Patient_Id Diabets
123 1A Yes
123 3D No
333 9G Yes
.
.
.
Now in my report i need to find total blood tests done by the doctors including only those patients who do not have diabetes.
Each doctor has got different set of patients .
Please help me guys
Thanks
March 30, 2009 at 9:04 pm
Like this:
Select Doctor_ID, Patient_ID, Count(*) as [Count]
From Table1 as t1
Inner Join Table2 as t2 ON t1.Doctor_ID = t2.Doctor_ID
And t1.Patient_ID = t2.Patient_ID
Where t2.Diabetes = 'No'
Group by t1.Doctor_ID, t1.Patient_ID
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 2:18 am
Now in my report i need to find total blood tests done by the doctors
Select Doctor_ID, Patient_ID, Count(*) as [Count]
From Table1 as t1
Inner Join Table2 as t2 ON t1.Doctor_ID = t2.Doctor_ID
And t1.Patient_ID = t2.Patient_ID
Where t2.Diabetes = 'No'
Group by t1.Doctor_ID, t1.Patient_ID
If you just want to find out the total blood tests done by each docotor, you can remove patient_id from SELECT and GROUP By clause....
Also, I think you have to take sum(Table1.blood_tests) to find total blood tests done and not Count(*) . Just verify it once pls...
March 31, 2009 at 7:15 am
swmsan (3/31/2009)
Now in my report i need to find total blood tests done by the doctors
Select Doctor_ID, Patient_ID, Count(*) as [Count]
From Table1 as t1
Inner Join Table2 as t2 ON t1.Doctor_ID = t2.Doctor_ID
And t1.Patient_ID = t2.Patient_ID
Where t2.Diabetes = 'No'
Group by t1.Doctor_ID, t1.Patient_ID
If you just want to find out the total blood tests done by each docotor, you can remove patient_id from SELECT and GROUP By clause....
Also, I think you have to take sum(Table1.blood_tests) to find total blood tests done and not Count(*) . Just verify it once pls...
Good point on the patient ID.
However, on the Blood Tests, I assumed that the column values were ID's and not counts. We would need clarification from "Nuts" on that...
Here is the modified code
Select Doctor_ID, Count(t1.BloodTests) as [Count]
From Table1 as t1
Inner Join Table2 as t2 ON t1.Doctor_ID = t2.Doctor_ID
And t1.Patient_ID = t2.Patient_ID
Where t2.Diabetes = 'No'
Group by t1.Doctor_ID
If the contents of "BloodTests" in table 1 are counts and not ID's then change the Count function above to a SUM(t1.BloodTests) instead.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 31, 2009 at 7:51 am
Table 1
Doctor_ID Patient_ID Blood_Tests
123 1A 4
123 1B 67
222 6A 4
333 9G 756
.
Yeah....We need a clarification on this.
Since '4' has been given as the blooad_tests for doctor_id ids 123 & 222, I assume it's no. of tests done..Also the field name Blood_Tests sounds same. (I know that its just an eg. and dummy data.....)
March 31, 2009 at 11:24 am
Hi
Looks like data is no good. but the answer provided by RBarryYoung is good to solve your problem.
Thanks -- Vijaya Kadiyala
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply