Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Group top 5 (2003)

    I have a table that has provider, therapeutic class, brand, number of scripts and dollars paid. What I am trying to do is find the top 5 therapeutic classes by provider based on dollars paid(I can do this). Next I want to find the top 5 drugs under the therapeutic class and list these on a report with the therapeutic class as the main grouping and then the top 5 drugs under this therapeutic class. How would I be able to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  3. #3
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Group top 5 (2003)

    Would this be different in the fact I have to find the top 5 therapeutic classes and then find the top 5 brands under the therapeutic classes?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group top 5 (2003)

    You'd have to create several queries: a top 5 query for the therapeutic classes, and then a query based on that one using the method described in the MSKB article. If you want help with that, I'd have to see (a stripped down copy of) the database.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Group top 5 (2003)

    Hans,

    I have the top 5 Therapeutic class query in descending order. Next I created another query which links the top 5 query back to the original data and linked to provider id and therapeutic class and sorted by net descending. It works great except the query brings back more than 5 brands. Am I on the right track? I have some work to do on subquerying for it is my weakness. It would be hard to strip down the database because it has personal information in the tables and healthcare data is protected.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group top 5 (2003)

    You could create some dummy records. See <post#=401925>post 401925</post#> for instructions.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Group top 5 (2003)

    Here is the stripped down database and the first query I have to give me the to 5 therapeutic classes.
    Attached Files Attached Files
    • File Type: zip X.zip (63.5 KB, 1 views)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group top 5 (2003)

    Could you please post a database with sufficient records to test a top 5 query? Your table contains only 2 records for "PCP Prescriber" so there isn't anything to do.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Group top 5 (2003)

    I have added some more records. Thanks.
    Attached Files Attached Files
    • File Type: zip X.zip (21.4 KB, 1 views)

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group top 5 (2003)

    Thanks, that was helpful. I have created the query in the attached version.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Jun 2005
    Location
    Atlanta, Georgia, USA
    Posts
    208
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Group top 5 (2003)

    Thanks for your help.. Does the t stand for temp?

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Group top 5 (2003)

    Where is the t that you are asking about?
    Regards
    John



  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group top 5 (2003)

    It's just an alias for the table name, to distinguish the name of the table in the subquery from the same table in the main query. It could have been anything. The alias is specified in the <code>"AS t"</code> part.
    <code>
    SELECT TOP 5 t.[Brand Name] FROM [Current Month Table2] <big>AS t</big> WHERE t.[PCP ID]=[Current Month Table2].[PCP ID] AND t.[Master Therapeutic Class]= [Current Month Table2].[Master Therapeutic Class] GROUP BY t.[PCP ID], t.[Master Therapeutic Class], t.[Brand Name] ORDER BY Sum(t.[NET Paid]) DESC)</code>

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query Group top 5 (2003)

    The t is used in the subquery in the criteria for the Brand Name field. I could have named it coffee instead. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query Group top 5 (2003)

    Yes I see now. I did not look far enough.
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •