claim_diagnosis

Provides information about the ICD diagnosis codes associated with a claim. https://www.cms.gov/medicare/coding-billing/icd-10-codes

Primary Key:

• claim_id
• diagnosis_type
• claim_diagnosis_index

Checks:

• claim_diagnosis_diagnosis_code_check - ((diagnosis_code = upper(diagnosis_code)) AND (diagnosis_code <> ''::text))

Foreign Keys:

• claim_id => claim.claim_id
• icd_diagnosis_id => icd_diagnosis_lookup.icd_diagnosis_id

Indexes:

• claim_diagnosis_claim_id_diagnosis_type_idx - claim_id, diagnosis_type
• claim_diagnosis_diagnosis_type_uq (Unique) - claim_id, diagnosis_type, diagnosis_code
• claim_diagnosis_icd_diagnosis_id_idx - icd_diagnosis_id
• claim_diagnosis_principal_uq (Unique) - claim_id

Column	Data Type	Nullable	Indexed	Description
claim_id	bigint	No	Yes	Link to the claim record. Generated through expression:
diagnosis_code	text	No	Yes	ICD diagnosis code (Loop 2300 HI / UB04 FL67 when diagnosis_type is 'Principal', FL67a-q when 'Other', FL69 when 'Admitting', FL70 when 'Patient Reason for Visit', FL72 when 'External Cause of Injury' / HCFA item 21). Generated through expression:
icd_version	text	No	No	ICD version, usually 10. (UB04 FL66 / HCFA item 21) Generated through expression:
diagnosis_type	diagnosis_code_type	No	Yes	Type of diagnosis code. Can be 'Principal', 'Admitting', 'Patient Reason for Visit', 'External Cause of Injury', or 'Other'. Generated through expression:
present_on_admission	yes_no_response_type	Yes	No	Indicator used to determine whether a condition or diagnosis was already present when a patient was admitted. Spec types possible are 'No', 'Unknown', 'Not Applicable', 'Yes'. (Loop 2300 HI) Generated through expression:
icd_diagnosis_id	bigint	Yes	Yes	Link to the ICD diagnosis record. Generated through expression:
claim_diagnosis_index	smallint	No	Yes	Order of diagnosis codes found in the claim. Indexes are 0-based so the first diagnosis code in the claim will have an index of 0, second will have an index of 1 and so on. Generated through expression: