0 Replies Latest reply: Jul 15, 2010 12:40 PM by Sally Hurley RSS

    Getting the most recent value that may not be the maximum effective date

    Sally Hurley

      Greetings.

      I am trying to calculate RVU Unit values for charges. Basically, I have two tables:

      CHARGES
      PostDate
      ProcedureCode
      Units

      RVUPROFILES
      ProfileName
      ProcedureCode
      RVUAmount
      EffectiveDate

      In the RVUProfiles table, there are multiple profiles for each procedure code, and each may have multiple effective dates with an associated RVU Amount. The user may choose one and only one Profile Name. I want to display, for each charge record, the RVU units for that charge (Units * RVUAmount). I can link by ProcedureCode easy enough -- but picking the right RVUAmount has proved to be tricky. I thought I could do the following to grab the right amount:

      FIRSTSORTEDVALUE(RVUAmount, -EffectiveDate)

      But, unfortunately, if the PostDate of the charge was 01/03/2008, and there are RVUProfile records for that procedure code with effective dates of 01/03/2008 and 01/01/2009, it pics the RVUAmount for the 01/01/2009 effective date, instead of the appropriate 01/03/2008. I have tried several calculations, including set analysis and if statements... and nothing seems to work. I fear I am making this more complicated than it needs to be, but I'm not seeing a solution.

      Please find attached a copy of my QVW. The bottom chart is the one in question (RVU Units). The fields I am trying to get to fill in is the RVU Units and Amount (procedure code 99231 is the troubling one).

      Oh - I'm on v8.5 - if that makes a difference.

      Thanks!

      Sally