Results 1 to 3 of 3

Thread: Stupid (?) Query Question

  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Red face 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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Can you post table schema?

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    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
  •