-
Stupid (?) Query Question
I am totally self taught here, so bear with me. There is probably a simple solution to this that I have not figured out. I have written a query to pull the number of patients referred by each physician in our database. I will need to make a report from this that will show the various statistics (average length of stay, diagnosis, etc) per patient for each physician who referred > 5 in a given year. I am assuming that it would be best to make a few different views to accomplish everything, but I am currently stumped at the > 5 part. The jist of what I have for that is the following:
SELECT DR.NAME, PT_ADMIT.ADMIT_DATE
FROM PT_ADMIT INNER JOIN
DR ON PT_ADMIT.REFERRAL_ID = DR.DOCTOR_ID
WHERE (PT_ADMIT.ADMIT_DATE BETWEEN '01-01-2008' AND '01-01-2009')
GROUP BY DR.NAME, PT_ADMIT.ADMIT_DATE
I tried adding "HAVING COUNT(DR.NAME) > 5", but that gave me unexpected results. I ended up pulling the count in the Reporting Services report because I am unsure how to do it via query. I am using a SQL 2005 database with SSMSE 2005, BIS, and Reporting Services. What am I doing wrong?
Thanks in advance for your help.
Last edited by barb951; 11-19-2008 at 01:07 PM.
-
Can you post table schema?
-
I finally got it to work, but it seems like it was a roundabout way. I created a view from the query I posted and then ran the subquery:
SELECT DOCTOR_ID
FROM REFCOUNT
GROUP BY DOCTOR_ID
HAVING COUNT(DOCTOR_ID) >5
The relevant columns from the tables are:
DR
name (varchar(67), null)
doctor_id (PK,int, not null)
PT_ADMIT
admit_date (datetime, null)
referral_id (FK, int, null)
Thanks for your help!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|