Results 1 to 15 of 15

Thread: Query Help???

  1. #1
    Join Date
    Aug 2009
    Posts
    9

    Unanswered: Query Help???

    Whats wrong?

    My error:
    Msg 170, Level 15, State 1, Line 7
    Line 7: Incorrect syntax near 'max'.

    Here is my query:
    Select distinct
    cc.Mbr_ID,
    cc.MbrLastNm,
    cc.MbrFirstNm,
    count (*) as Num_fills,
    sum(cc.DaysSupply) as Totdaysupply
    max(cc.INLOAD_DATE+cc.DaysSupply),
    max(cc.RxFillDate+cc.DaysSupply)-cc.INLOAD_DATE as Duration
    from dbo.CAREMARK_CLAIMS cc
    Group by
    cc.Mbr_ID,
    cc.MbrLastNm,
    cc.MbrFirstNm

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    You are missing the comma after Totdaysupply

  3. #3
    Join Date
    Aug 2009
    Posts
    9
    Thanks, now how do I change this in SQL from SAS programming: I need to make it so that I can run SQL Enterprise manager.
    Proc sort data = claims by member_id fill_dt; run;

    proc transpose data = claims out=days_supply (drop_name_) prefix =fill_dt; var fill_dt; run

    proc transpose data = claims out=days_supply (drop_name_) prefix = days_supply; by member_id; var days_supply' run;

    data both;
    merge fill_dates days_supply; by member_id;
    format start _dt end_dt mmddyy10.;
    start_dt=fill_dt1;
    end_dt=fill
    _dt1+179;
    run;

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I have no idea what you are talking about.
    What is SAS programming?

    The (unformatted) code you posted is definitely not SQL nor any SQL dialect I have ever seen.

  5. #5
    Join Date
    Aug 2009
    Posts
    9
    Ok lets try this then, how about if I wanted to calculate the MPR which is to sum the days of supply for a medication over a time period using a SQL procedure. this time period may be a fixed time interval or the length the patient used the medication, for instance date of last claim. Can we use a fixed time interval by truncating the days of utilization that falls after the interval, so that we can assure that all patients are reviewed over a similar time interval (all have the same denominator - 180-day)???

  6. #6
    Join Date
    Aug 2009
    Posts
    9

    Error - please help?

    Msg 174, Level 15, State 1, Line 7
    The max function requires 1 arguments.


    Here is my query:

    Select distinct
    cc.Mbr_ID,
    cc.MbrLastNm,
    cc.MbrFirstNm,
    count (*) as Num_fills,
    sum(cc.DaysSupply) as Totdaysupply,
    max(cc.ProcessingCutoffDate+cc.DaysSupply,max(cc.R xFillDate+cc.DaysSupply)) - cc.ProcessingCutoffDate as duration
    from dbo.CAREMARK_CLAIMS cc
    where(cc.RxFillDate>= '2009-01-01 00:00:00.000' and cc.RxFillDate <= '2009-06-30 23:59:59.000')
    Group by
    cc.Mbr_ID,
    cc.MbrLastNm,
    cc.MbrFirstNm

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this, it should run with no problem...
    Code:
    SELECT cc.Mbr_ID
         , cc.MbrLastNm
         , cc.MbrFirstNm
         , COUNT(*) AS Num_fills
         , SUM(cc.DaysSupply) AS Totdaysupply
      FROM dbo.CAREMARK_CLAIMS cc
     WHERE cc.RxFillDate >= '2009-01-01'
       AND cc.RxFillDate  < '2009-07-01'
    GROUP 
        BY cc.Mbr_ID
         , cc.MbrLastNm
         , cc.MbrFirstNm
    now we can discuss the missing calculations, yes?

    please explain what else you need, and keep in mind that you can't take a MAX of a MAX
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2009
    Posts
    9

    Lets discuss?

    Max(index_dt+days_supply,max(fill_dt+days_supply))-index_dt as duration....

    index_dt = enddate or last fill date

    What I want to do is figure out how to calculate how long the patient used the medication from the first day of therapy to the last.

    To calculate mpr = Total days supply/180

    For ex. a patient has 3 claims over a 180 day period the date of first claim (index date) is the first day of the study period and the end of study period is 180 days after the date of the first claim. how do I write that in SQL?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jb357
    Max(index_dt+days_supply,max(fill_dt+days_supply))-index_dt as duration....
    sorry, you cannot take a MAX of a MAX

    other than that, i did not understand your explanation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Aug 2009
    Posts
    9
    error - Msg 170, Level 15, State 1, Line 10
    Line 10: Incorrect syntax near 'merge'.

    query?
    Select distinct
    cc.Mbr_ID,
    cc.MbrLastNm,
    cc.MbrFirstNm,
    cc.RxFillDate,
    cc.DrugName,
    count (*) as Num_fills,
    sum(cc.DaysSupply) as Totdaysupply,
    max(cc.RxFillDate+cc.DaysSupply)as Duration
    merge(cc.rxfilldate, cc.dayssupply) by cc.mbr_Id
    from dbo.CAREMARK_CLAIMS cc
    where(cc.RxFillDate>= '2009-01-01 00:00:00.000' and cc.RxFillDate <= '2009-06-30 23:59:59.000')
    and cc.Therapeutic_Code in ('240824','402800','402892','402808','243204','242 400')
    Group by
    cc.Mbr_ID,
    cc.MbrLastNm,
    cc.MbrFirstNm,
    cc.RxFillDate,
    cc.DrugName

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Merge is not an aggregate function. It is a new statement similar to update and insert, though.

  12. #12
    Join Date
    Aug 2009
    Posts
    9
    So what do I put instead ???

  13. #13
    Join Date
    Aug 2009
    Posts
    9
    So what do I put instead ??? cAn you fix my query to work?

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what did you want the "merge" to accomplish?

    and why did you stick DISTINCT back into the query?

    speaking just for myself, if you want my help, you have to go back to post #7 and work forward from there
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Aug 2009
    Posts
    9
    oops sorry about that forgot to copy - what I am trying to do I would like to see the date of first rxfilldate of first claim to the last period of my analysis basically 180 days I am pulling (6months) also how can I query to have all the claims for each member together rather than scattered.

    SELECT cc.Mbr_ID
    , cc.MbrLastNm
    , cc.MbrFirstNm
    , cc.rxfilldate
    , cc.drugname
    , COUNT(*) AS Num_fills
    , SUM(cc.DaysSupply) AS Totdaysupply
    , max(cc.RxFillDate+cc.DaysSupply)as Duration
    FROM dbo.CAREMARK_CLAIMS cc
    WHERE cc.RxFillDate >= '2009-01-01'
    AND cc.RxFillDate < '2009-07-01'
    AND cc.Therapeutic_Code in ('240824','402800','402892','402808','243204','242 400')
    GROUP
    BY cc.Mbr_ID
    , cc.MbrLastNm
    , cc.MbrFirstNm
    , cc.RxFillDate
    , cc.DrugName

Posting Permissions

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