Post reply

Sum on a row

  • October 11, 2006 at 12:23 pm

    #192708

    I am retrieving data and I want to get Drug Name, Total Amount Paid between jan'06 -ytd, and total prescription for a particular drug. But the issue I am having is 1) the sum function seems like its not working and the drug column is not unique. Any advice?

    the following is my query:  

    SELECT 

     

      Drug_Name  AS Drug

     , Therapeutic_Class_AHFS_Code

     , COUNT(Drug_Name) AS Prescriptions

     , SUM(Plan_Pay_Amount) AS [benefits paid]

    FROM         dbo.tblPharmacy

    WHERE     (Date_Filled BETWEEN CONVERT(DATETIME, '2006-01-01 00:00:00', 102)

     AND CONVERT(DATETIME, '2006-07-31 00:00:00', 102))

     and (LPO_Name = 'BEAUMONT LPO')

     

    GROUP BY Drug_Name

     , Therapeutic_Class_AHFS_Code

     , Plan_Pay_Amount

     

    ORDER BY drug_name

     

    a sample of my result is:

    Drug 

    Therapeutic_Class_AHFS_CodePrescriptions   benefits paid

    ABILIFY      TAB 10MG2816080040
    ABILIFY      TAB 15MG281608001294

    Is there a way to combine these to get total prescriptions and benefits paid?

  • October 11, 2006 at 4:03 pm

    #665326

    Do you mean something like:

    SELECT
  LEFT(Drug_Name, CHARINDEX(' ', Drug_Name) as Drug,
  ...
ORDER BY 
  LEFT(Drug_Name, CHARINDEX(' ', Drug_Name)
  ....

    Hope this helps



    Mark

  • October 11, 2006 at 4:39 pm

    #665331

    Thanks Mark, that gave me the same results.  I might have to create a lookup table with a truncate drug name in order to total prescription and pay amount.

     

    Thanks for the help

  • October 11, 2006 at 10:03 pm

    #665360

    Mark left out one part and if you did, too, you WILL get the same results... your GROUP BY has to be by the same formulas that Mark gave you just like they are in the ORDER BY he gave you...

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 12, 2006 at 5:34 am

    #665434

    Thanks, Jeff.  GROUP BY was what I intended.  Just a cut and paste error.



    Mark

  • October 12, 2006 at 7:15 am

    #665467

    Thanks guys, that helped!!

  • October 12, 2006 at 7:25 am

    #665472

    Yep... I knew that, Mark I just didn't think that Shine recognized that because he said your code change didn't work and I knew it would (as usual, I may add).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply