Report Writing

Interoperability and EHR Optimization

Privacy and Security

Testing Code for ICD-10

Written by Thomas Harlan, Jim McGrath; Reporting Services Team - iatricSystems

Testing Code for ICD-10

October 1 is coming fast, which means that this year (2015) US healthcare finally gets to join the rest of the world in using the ICD-10 codeset for diagnoses.

MEDITECH is already in reasonable shape for the ICD-10 conversion because their overseas customers have been using ICD-10 for many years. The data structures in the DR, therefore, reflect the proper storage sizes and code-sets.

Where trouble looms its ugly head for a facility, however, is:

  • Files going out to a vendor need to be tested and verified for being able to properly send ICD-10 codes.
  • Vendors may request dual-coded (ICD-9 and ICD-10) files (or pairs of files for the same account(s).
  • Reports or extracts may have hard-coded lists of ICD-9 codes embedded in their logic.
  • Select lists on a report may either have hard-coded lists of ICD-9 codes or may pass ICD-9 related parameters that are too small, now that ICD-10 has bigger possible code sizes.

There are more than 250 tables in the DR that contain diagnosis codes, or references thereto, but the two primary ones of interest are:

livedb.dbo.AbsDrgDiagnoses2 – Main dual-coded diagnoses

Plus possibly:

livedb.dbo.AbsDrgDataDualCodes – Dual-coded admitting diagnosis codes
livedb.dbo.AbsDrgProcedures2 – Dual-coded Procedures

A further complication is added when you need to generate files from your Test ring data, and you likely have a combined LIVE+TEST DR (rather than each database on a separate server as we’d recommend).

Initial Spadework

  • Talk to your ICD-10 conversion team (HIM, plus the billing analysts) and get a list of account numbers currently in TEST with ICD-10 coding.
  • Also find out if they are dual-coding accounts in LIVE, currently, and get a list of those accounts.
  • Find out who on the ICD-10 implementation team can provide you with ICD-10 code equivalencies for ICD-9 codes that are hard-coded in your reports or extracts.
  • Identify which reports display or use diagnosis codes.
  • Using that list of reports, contact either the HIM ICD-10 team or key user areas (like Performance Improvement or Clinical Decision Support) to see which reports they need to validate against dual-coded accounts.
  • Identify which vendor extracts you have in play, and whether they contain diagnosis code information.
  • From that list of vendors, collect the latest contact information for their technical representative.
  • Reach out to each vendor:
    • Let them know you are starting to test extracts for ICD-10, including theirs.
    • Query them to see if they want an ICD-10 test file and/or a dual-coding test file.
    • Get the file naming convention and layout specifications for ICD-10 test files.
    • Get the file naming convention and layout specifications for dual-coding test files.
  • Get to work generating test files and validating reports!

First Pass at Converting ICD-9 to ICD-10

If your HIM/ICD-10 team is overloaded, you might be able to help them by taking a crack at pre-translating the ICD-9 codes to ICD-10 using a utility like:

But do not put any codes into production without review and approval of your HIM/ICD-10 team!

Warning about Duplicated Codes

You must be aware that there are some codes in ICD-9 which have near-duplicates in ICD-10 with vastly different meanings. The codes are differentiated by having embedded periods in ICD-10, so it is absolutely necessary to not remove any letters or punctuation from the codes.

Finding Dual Coded Accounts

Note! If HIM does not have a list of accounts they’ve been dual coding on… you can generate it yourself:

SELECT ABD.AccountNumber
FROM livedb.dbo.AbsDrgDiagnoses2 DX2
JOIN livedb.dbo.AbstractData ABD ON ( ABD.AbstractID = DX2.AbstractID AND
                                                      ABD.SourceID = DX2.SourceID )
GROUP BY ABD.AccountNumber ;

Remember to check TEST as well!

Stored Procedures Using Diagnosis Codes

If you’ve based all of your reports and extracts off of stored procedures (and you did follow that best practice, didn’t you?), you can do a search like this to find stored procedures that use the diagnosis code table:

SELECT OBJECT_NAME(SSM.object_id) AS ObjectName
,SOB.type_desc AS ObjectType
FROM sys.sql_modules SSM
LEFT JOIN sys.objects SOB ON ( SOB.object_id = SSM.object_id )
WHERE SSM.[definition] LIKE '%AbsDrgDiagnoses%'

Testing Reports

Now you’ve got a list of stored procedures and reports to review:


  • Review the stored procedure(s) you are using in a given report looking for CAST or CONVERT or LEFT function calls that might wrap around AbsDrgDiagnosis.Diagnosis and reduce it to less than thirty (30) characters.
  • Review the stored procedure(s) for hard-coded lists of diagnosis codes. Contact your HIM team to get lists of equivalent ICD-10 codes.
  • Remediate the stored procedure(s) as necessary; remembering to add the new ICD-10 codes to any lists. Your users will still be running reports against pre-Oct 1 date ranges, and you don’t want the reports to break unexpectedly.
  • In this scenario it is safe to update the stored procedures in LIVE before the October 1 cutover, as you want to ensure the reports are still working with ICD-9 coding.
  • With the stored procedure(s) updated, review the reports looking for:
    • Field formatting that would truncate a diagnosis code field to less than six (6) characters.
    • Field formatting that would truncate a diagnosis description to less than 250 characters.
    • Check for grouping formulas utilizing lists of ICD-9 codes in the report.
    • Check for report-level filters utilizing ICD-9 codes in some way. (By the way, report-level filtering should always be reviewed. Sometimes it is the way to go, but mostly not).
    • Watch out for code that would strip off any letters or periods in the ICD-10 code. That is no longer an advisable behavior, as we have noted above.
  • Remediate as necessary.
  • Hand off to your end-users to test.
  • Rinse and repeat!


  • This is painful. You need to join to two different tables (AbsDrgDiagnoses and AbsDrgDiagnoses2) and then group the results appropriately.
  • Make a new version of your affected stored procedure.
  • In the code, when you JOIN to the regular table (AbsDrgDiagnoses) replace that simple JOIN with a derived-table JOIN which looks like this:
,ODX.SourceID AS SourceID
,ODX.AbstractID AS AbstractID
,ODX.Diagnosis AS DiagnosisID
,ADI.[Name] AS DiagnosisName
FROM livedb.dbo.AbsDrgDiagnoses ODX
LEFT JOIN livedb.dbo.AbsDrgData DRG ON ( DRG.AbstractID = ODX.AbstractID
AND DRG.SourceID = ODX.SourceID )
LEFT JOIN livedb.dbo.DAbsDiagnoses ADI ON ( ADI.DiagnosisCodeID = ODX.Diagnosis AND
ADI.GrouperVersionID = DRG.GrouperVersion AND
ADI.SourceID = DRG.SourceID )
,DDX.SourceID AS SourceID
,DDX.AbstractID AS AbstractID
,DDX.DiagnosisCodeID AS DiagnosisID
,ADI.[Name] AS DiagnosisName
FROM livedb.dbo.AbsDrgDiagnoses2 DDX
LEFT JOIN livedb.dbo.DAbsDiagnoses ADI ON ( ADI.DiagnosisCodeID = DDX.DiagnosisCodeID AND
ADI.GrouperVersionID = DDX.AdmDrgVersionID AND
ADI.SourceID = DDX.SourceID )
) CDX ON ( CDX.AbstractID = <Table with AbstractID> )
  • Oh, now what in the world is that? This code will generate (behind the scenes) a temp table, fill it with both the regular and dual-coding tables, stick them together and pull the whole thing back so that you can JOIN to it…
  • Update your stored procedure code to use DiagnosisID instead of Diagnosis.
  • Note that we’re using the DiagnosisName from the dictionary table, instead of the embedded version in the AbsDrgDiagnoses table. That embedded-possessive field will be going away soon!
  • Add the CodeSet field to your stored procedure.
  • Test the stored procedure… fix as necessary.
  • Now edit the report RDL to bring in that new CodeSet field and group by it/display in the output.
  • Note that this is not the optimal way to do this! This is the fastest way to crank through some reports so they can be validated.

Testing Extracts

This example assumes you have one or more extracts being generated from the DR, using MS SQL Server Integration Services (SSIS), from the LIVE database, with your custom stored procedures in a zcus database:

Single Coding

  • Make a copy of the current stored procedure code (ABS_iatric_pg_qio_v2_sp, for example) with a new name (ABS_iatric_pg_qio_icd10_sp).
  • In the new stored procedure code, update all of the references of your LIVE database to your TEST database (livedb > testdb, for example).
  • Important! Check the code to see if it references any lists of ICD-9 codes.
  • In our example we find that the extract does use lists of ICD-9 procedure codes (to identify MHA QI or OP-29 or OP-30 visits) stored in a custom table – now we need to send that list of codes to the HIM ICD-10 team to get equivalent codes identified.
  • Update your custom lists of codes by adding the new equivalents. You will have a cutover month, most likely, where you will need both sets of codes in place.
  • Run your script to create the new stored procedure in your zcus container, now pointing at TEST instead of LIVE.
  • Run the stored procedure from SQL Server Management Studio (or the SQL editor of your choice) to confirm that it works and you’re getting data from TEST. Note! Generally TEST does not have much data in it… so you may need to run your query for a multiple-month range to get something useable. The list of accounts you collected above will help.
  • Now make a copy of the SSIS .dtsx file (ABS_iatric_pg_qio.dtsx) as (ABS_iatric_pg_qio_icd10.dtsx).
  • Update the stored procedure referenced in the package (you’re doing that with a package-level variable, right?) to use the new stored procedure from zcus (ABS_iatric_pg_qio_icd10_sp).
  • Update the package to put the new test file in the proper output folder, with whatever name the vendor requested (and you’re controlling the output path and file name in a package-level variable, right?)
  • Run the package, which should use the new stored procedure and the new output path.
  • Dispatch the file to the vendor.
  • Rinse and repeat for each additional extract.

Dual Coding

This is more complicated, because the vendor either wants:

  • A completely new file format with both kinds of codes.
  • Two (2) files, one with the ICD-9 codes and one with the ICD-10 codes, for the same set of accounts.
  • A single file, with the dual-coded ICD-10 codes that match up to accounts already sent to the vendor in the regular extract.

So you want to have that conversation with each vendor as soon as possible! Once you’ve sorted out what to send:

Scenario 1 (New Format)

  • This is the rough one. Build a whole new extract, starting from ABS.PAT.main (dbo.AbstractData) which gets data from both dbo.AbsDrgDiagnoses and dbo.AbsDrgDiagnoses2.
  • Depending on the format, that may be non-trivial.

Scenario 2 (Two files, one per Format)

  • This one is easier, particularly if you’ve pre-identified the dual-coded accounts as we noted above.
  • Duplicate the stored procedure fetching the diagnosis codes, update it to use dbo.AbsDrgDiagnoses2. Hard-code (for these testing purposes) the list of account numbers you’ve identified into the main WHERE.
  • Duplicate your SSIS package, adding a new Task to generate the ICD-10 codes, using the new stored procedure, to generate a new file.
  • Duplicate your standard ICD-09 diagnosis codes stored procedure to a new stored procedure, and hard-code the list of account numbers into the main WHERE.
  • The result is a one-time package that pulls the specific set of account numbers. Which is fine for the purpose, particularly as we may need to pull the same set of accounts more than once for the vendor.

Scenario 3 (Single New File)

  • This is easiest, particularly if you’ve pre-identified the dual-coded accounts in the last six months or so.
  • Duplicate the stored procedure fetching the diagnosis codes, update it to use dbo.AbsDrgDiagnoses2 and the list of account numbers pre-identified.
  • Duplicate your SSIS package, adding a new Task to generate the ICD-10 codes, using the new stored procedure, to generate a new file.
  • The result is a one-time package that pulls the specific set of account numbers. Which is fine for the purpose, particularly as we may need to pull the same set of accounts more than once for the vendor.

Analysis Services

As you prepare for ICD-10 cutover, Iatric Systems can help you proactively identify NPR, RD and DR-based reports and extracts that will be impacted. We also offer remediation services to update reports and extracts. For more information, please contact your Iatric Systems Account Executive or email us at

Topics: Data Repository, ICD-10, MEDITECH, report writing

Subscribe to the Report Writing blog.

Receive the latest articles directly in your inbox.
Enter your email address and click SUBSCRIBE: