SQL Analysis on 2024 Midyear Final ICD10 Mappings
About
Unlocking Insights: Navigating ICD-10 Mappings and Crosswalks
ICD-10 mappings
ICD-10 mappings are associations or translations between codes in the International Classification of Diseases (ICD) system and other code systems or terminologies. These mappings are used to facilitate interoperability and data exchange between different healthcare information systems, coding schemes, and clinical terminologies.
ICD mappings can serve various purposes, including:
ICD mappings can serve various purposes, including:
- Crosswalks: Mapping between ICD codes and other code systems or terminologies, such as Current Procedural Terminology (CPT), Healthcare Common Procedure Coding System (HCPCS), or Diagnosis Related Groups (DRG).
- Conversion: Translation between different versions of the ICD, such as mapping between ICD-9-CM and ICD-10-CM.
- Standardization: Harmonization of codes across different healthcare domains or countries, ensuring consistent representation of diagnoses and procedures.
- Reporting and Analytics: Supporting data analysis, research, and reporting by linking ICD codes to other classification systems or databases.
ICD mappings might be used in electronic health records (EHR) systems to convert diagnosis codes entered by clinicians into standardized ICD codes for billing and reporting purposes. They might also be utilized in healthcare analytics platforms to analyze clinical data across different code systems.
The specific mappings required would depend on the context and purpose of use. Organizations involved in healthcare data management, coding, billing, research, and policy-making often utilize ICD mappings to ensure accurate and standardized representation of health information.
Observations and Codes
There are 2,661 rows of diagnosis codes that fully exist for all categories.
SELECT *
FROM ICD10_Payment_Codes_FY23_24
where [RxHCC_Model_Category_V08 for 2024_Payment Year] = ‘Yes’
and [CMS-HCC_ESRD Model_Category_V21 for 2024_Payment Year] = ‘Yes’
and [CMS-HCC_ESRD Model_Category_V24 for 2024_Payment Year] = ‘Yes’
and [CMS-HCC_Model_Category_V22 for 2024_Payment Year] = ‘Yes’
and [CMS-HCC_Model_Category_V24 for 2024_Payment Year] = ‘Yes’
and [CMS-HCC_Model_Category_V28 for 2024_Payment Year] = ‘Yes’
and [RxHCC_Model_Category_V05 for 2024_Payment Year] = ‘Yes’
and [RxHCC_Model_Category_V08 for 2024_Payment Year] = ‘Yes’
There are 640 diagnosis codes that exist only once for any category.
SELECT *
FROM ICD10_Payment_Codes_FY23_24
WHERE
(CASE WHEN [RxHCC_Model_Category_V08 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [CMS-HCC_ESRD Model_Category_V21 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [CMS-HCC_ESRD Model_Category_V24 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [CMS-HCC_Model_Category_V22 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [CMS-HCC_Model_Category_V24 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [CMS-HCC_Model_Category_V28 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [RxHCC_Model_Category_V05 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END +
CASE WHEN [RxHCC_Model_Category_V08 for 2024_Payment Year] = ‘Yes’ THEN 1 ELSE 0 END) = 1;
4,199 diagnosis codes that have at least one value more than or equal to 200
SELECT *
FROM ICD10_Payment_Codes_FY23_24
WHERE GREATEST([CMS-HCC_ESRD_Model_Category_V21],
[CMS-HCC_ESRD_Model_Category_V24],
[CMS-HCC_Model_Category_V22],
[CMS-HCC_Model_Category_V24],
[CMS-HCC_Model_Category_V28],
[RxHCC_Model_Category_V05],
[RxHCC_Model_Category_V08]) > 200;
Top 10 most common word in the description excluding words like 'of', 'with', 'unspecified', 'encounter', 'initial'.
WITH SplitWords AS (
SELECT
[Diagnosis_Code],
value AS Word
FROM ICD10_Payment_Codes_FY23_24
CROSS APPLY STRING_SPLIT(Description, ‘ ‘)
)
SELECT TOP 10 Word, COUNT(*) AS WordCount
FROM SplitWords
WHERE Word <> ”
AND Word NOT IN (‘of’, ‘with’, ‘unspecified’, ‘encounter’, ‘initial’, ‘other’, ‘left’, ‘right’, ‘and’, ‘for’, ‘to’,
‘or’, ‘intentional’, ‘type’, ‘open’, ‘without’, ‘due’, ‘by’, ‘chronic’, ‘malignant’, ‘closed’, ‘lower’)
GROUP BY Word
ORDER BY COUNT(*) DESC;
Top 10 least common word in the description excluding words like 'of', 'with', 'unspecified', 'encounter', 'initial'.
WITH SplitWords AS (
SELECT
[Diagnosis_Code],
value AS Word
FROM ICD10_Payment_Codes_FY23_24
CROSS APPLY STRING_SPLIT(Description, ‘ ‘)
)
SELECT TOP 10 Word, COUNT(*) AS WordCount
FROM SplitWords
WHERE Word <> ”
AND Word NOT IN (‘of’, ‘with’, ‘unspecified’, ‘encounter’, ‘initial’, ‘other’, ‘left’, ‘right’, ‘and’, ‘for’, ‘to’,
‘or’, ‘intentional’, ‘type’, ‘open’, ‘without’, ‘due’, ‘by’, ‘chronic’, ‘malignant’, ‘closed’, ‘lower’)
GROUP BY Word
ORDER BY COUNT(*) ASC;
ICD 10 Crosswalk
89 rows where sex condition is 'male'
310 rows where cc age split is not empty
1,017 rows where ICD reaches second cc
Only 1 row where ICD reaches third cc
2,432 rows where sex condition is 'male'
2,562 rows where age at diagnosis is not empty
Top 10 CC's (and how many they are in this document)
