- 683 Views
- Uploaded on
- Presentation posted in: General

How to Use MDS Data to Calculate Case Mix

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

**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?scid=kb;en-us;210504
DLookup() Usage, Examples, and Troubleshooting
Microsoft Knowledge Base Article
http://support.microsoft.com/default.aspx?scid=kb;en-us;208786

**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]","[TableToCalcDays]","[ID]=" & [ID]-1)),
Switch(
TableToCalcDays!AssessmentTypeCode="01",Null,
TableToCalcDays!AssessmentTypeCode In ('06','07','08'),
TableToCalcDays!AssessmentDate-(DLookUp("[AssessmentDate]","[TableToCalcDays]","[ID]=" & [ID]-1)),
TableToCalcDays!AssessmentTypeCode Not In ('01','06','07','08'),
TableToCalcDays!AssessmentDate-(DLookUp("[AssessmentDate]","[TableToCalcDays]","[ID]=" & [ID]-1))),
Null)
RUG
IIf(
[TableToCalcDays]![SSN]=(DLookUp("[SSN]","[TableToCalcDays]","[ID]=" & [ID]-1)),
Switch([TableToCalcDays]![AssessmentTypeCode]="01","",
[TableToCalcDays]![AssessmentTypeCode] In ('06','07','08'),(DLookUp("[StateCalculatedMedicaidRUG]","[TableToCalcDays]","[ID]=" & [ID]-1)),
[TableToCalcDays]![AssessmentTypeCode] Not In ('01','06','07','08'),[TableToCalcDays].[StateCalculatedMedicaidRUG]),
"")

**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)

**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

**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/