How to Use MDS Data to Calculate Case Mix

1
How to Use MDS Data to Calculate Case Mix

• Presentation by
• Randy K. Baker
• Case Mix 2003 Conference
• September 11, 2003

2
A step by step outline of how to calculate
nursing home case mix by using MDS data.

• List of Steps
• Discussion of Each Step
• Examples of Each Step
• Sample Database
• Interactive

3
Perspective of This Presentation

• State Medicaid Agency
• Case mix is used as part of rate setting
• Quarterly calculation of adjustments
• Other components in the rate
• This presentation just focuses on the case mix
  part of the rate calculation
• Intended to show example of calculation
• Sample Database

4
Before and After
5
What is Case Mix?

• RUG Case Mix Index (CMI) is a resource allocation
  methodology developed for continuing care
  residents. Each of the RUG groups has an
  associated CMI value that represents the relative
  cost of caring for an average continuing care
  resident within a group compared to the average
  resident in the population.
• Uses CMI can be used for resource allocation in
  continuing care facilities

6
Steps to Calculate Case Mix

• Gather MDS data in a manner that can be used in a
  PC database
• Load Data into a Microsoft Access Database
• Calculate the number of days each patient was on
  a RUG

• Calculate Facility Case Mix score
• Facility summary tables
• Reports of patient detail Reports of patient
  detail
• Administrative notifications

7
Gather MDS data in a manner that can be used in a
PC database

• Needed Fields (from MDS Form)
• Patient Identifier
• Facility Name / Number
• MDS Assessment Type
• Assessment Date
• RUG Score
• Other Identifiers

8
Load Data into a Microsoft Access Database

• Why Use MS Access?
• Not Promoting a product, rather features needed
  to perform functions
• Personal experience has shown MS Access to be a
  product that does the job

9
Load Data into a Microsoft Access Database
(Continued)

• Text Files
• Comma Delimited
• Tab Delimited
• Other Delimiter
• Fixed Length
• Other databases
• Direct links to data sources though ODBC
  connectivity

10
Why Use MS Access?

• Use large datasets
• Multiple steps needed to perform analysis
• Queries saved to be used again on new data
• Macros for performing multiple sequential tasks
• Calculations that cannot be done in a spreadsheet

11
Load Data into a Microsoft Access Database
(Continued)

• Create reusable template for loading data
• Automate load process
• Once routine has been validated, no longer need
  to do repetitive tasks

12
Load Data into a Microsoft Access Database
(Continued)

• Remember to include only those records which you
  want to include in the case mix (Medicare,
  Medicaid, Self-Pay, etc).
• Other identifiers are used to filter and select
  on for analysis and other data needs than just
  calculation of the case mix
• Data base demonstration

13
Load Data into a Microsoft Access Database
(Continued)

• Demonstration Screens
• Text File
• Layout File
• Delete Query
• Append Query
• Edit Query
• Calc Days Query
• Calc Case Mix Query

14
Calculate the number of days each patient was on
a RUG

• Calculating days involves comparing two dates
• Dates are in different records (i.e. each record
  has assessment date)

15
Calculate the number of days each patient was on
a RUG (continued)

• Technical Explanation of how to do a calculation
  when numbers are in different rows
• Referring to a Field in the Previous Record or
  Next Record
• Microsoft Knowledge Base Article
• http//support.microsoft.com/default.aspx?scidkb
  en-us210504
• DLookup() Usage, Examples, and Troubleshooting
• Microsoft Knowledge Base Article
• http//support.microsoft.com/default.aspx?scidkb
  en-us208786

16
Calculate the number of days each patient was on
a RUG (continued)

• Summary Explanation of how to do a calculation
  when numbers are in different rows

• Data must be physically in the order that
  calculation will be done in (first by facility
  and then by recipient identifier)
• Records must have a sequential ID number
• Reference to next or previous record refers to
  sequential ID number and uses the domain
  aggregate function Dlookup()

17
Calculate the number of days each patient was on
a RUG (continued)

• The DLookup() function in an expression (or
  calculation) that returns a field value in a
  domain, or specified set of records.
• It limits the records examined when performing
  calculation
• If the persons ID is the same as the persons ID
  of the previous record, then calculate date
  difference, otherwise do nothing.

18
Calculate the number of days each patient was on
a RUG (continued)

• Microsoft Access Demonstration of date
  calculation
• Append data from table to a new table in
  sequential order
• Calculate days between dates with update query
• Select RUG to be used in days calculation
• Calculate Days only when assessment is other than
  admission assessment

19
Case Mix Calculation Formula

• Days
• IIf(
• TableToCalcDays!SSN(DLookUp("SSN","TableToCalc
  Days","ID" ID-1)),
• Switch(
• TableToCalcDays!AssessmentTypeCode"01",Null,
• TableToCalcDays!AssessmentTypeCode In
  ('06','07','08'),
• TableToCalcDays!AssessmentDate-(DLookUp("Assessme
  ntDate","TableToCalcDays","ID" ID-1)),
• TableToCalcDays!AssessmentTypeCode Not In
  ('01','06','07','08'),
• TableToCalcDays!AssessmentDate-(DLookUp("Assessme
  ntDate","TableToCalcDays","ID" ID-1))),
• Null)
• RUG
• IIf(
• TableToCalcDays!SSN(DLookUp("SSN","TableTo
  CalcDays","ID" ID-1)),
• Switch(TableToCalcDays!AssessmentTypeCode"01"
  ,"",
• TableToCalcDays!AssessmentTypeCode In
  ('06','07','08'),(DLookUp("StateCalculatedMedicai
  dRUG","TableToCalcDays","ID" ID-1)),

20
Calculate the number of days each patient was on
a RUG (continued)

• Reminder!
• Dont get caught up in technical details if they
  dont make sense at first
• Remain focused on the basic concepts of what you
  are doing

21
Calculate the number of days each patient was on
a RUG (continued)
Time Periods For Date Calculation
Time period for analysis
Dates included in case mix data run
Days from last assessment to end of analysis
period
Days from beginning of analysis period to first
assessment date
22
Facility Summary Tables

• Summarize data by the number of days for each RUG
  by facility
• RUG Weight
• Days X Weight
• Total Facility Days
• Total calculated days

23
Facility Summary Tables(Continued)

• Demonstration of Summary queries

24
Calculate Facility Case Mix Score

• Case Mix
• Calculation
• Sum of the days for each RUG X RUG Weight
• Divided by
• Total Facility Days

Sum of (Days for Each RUG X RUG Weight)
Total Facility Days
25
Calculate Facility Case Mix Score (Continued)

• Demonstration of queries with calculations and
  formulas

26
Administrative Notifications

• Presentation has focused on how to calculate a
  case mix
• Dont forget to complete work by notifying those
  affected of the results of the calculations

27
Summary of Database Steps

• Load data from external source into table called
  MdsData
• Step 1 Macro
• Copy data in MdsData table to TableToCalcDays
• Step 2 Macro
• Orders data to perform calculations
• Run update query to calculate days
• Step 3 Macro
• Query UpdtDaysRugs

• Total days each resident was on a RUG
• Query
• DaysAndRugs-Detail
• Total the number of days for each RUG by facility
• Query
• DaysAndRugs-Sum
• Total the number of days for each facility
• Divide the (weight X days) by total days
• Query
• DaysAndRugs-Total

28
Summary of Case Mix Calculation Steps

• Presentation focus on basic steps
• Can have variations based on scope of desired
  calculation
• Brief exposure to another tool
• Assists with documentation

29
Review What is Case Mix?

• Case Mix Index values represents the relative
  cost of caring for an average continuing care
  resident within a group compared to the average
  resident in the population.
• Uses CMI can be used for resource allocation in
  continuing care facilities
• I now know how to calculate it myself!

30
Copies of Database and Presentation

• http//www.estrat.net/casemix/