cause_concept_id

SUS Inpatient Death

Source column DiagnosisICD. Resolve ICD10 codes to OMOP concepts. If code cannot be mapped, map using the parent code.

• DiagnosisICD Primary Diagnosis PRIMARY DIAGNOSIS

;with primarydiagnosis as (
	select *
	from omop_staging.sus_ICDDiagnosis
	where IsPrimaryDiagnosis = 1
)

select
	apc.NHSNumber as nhs_number,
	max(apc.DischargeDateFromHospitalProviderSpell) as death_date,
	max(apc.DischargeTimeHospitalProviderSpell) as death_time,
	max(d.DiagnosisICD) as DiagnosisICD
from omop_staging.sus_APC apc
	left join primarydiagnosis d
		on apc.MessageId = d.MessageId
where
	apc.NHSNumber is not null and
	apc.DischargeDateFromHospitalProviderSpell is not null and
	(
		apc.DischargeMethodHospitalProviderSpell = '4' -- "Patient died"
		or
		(
			apc.DischargeDestinationHospitalProviderSpell = '79' -- Not applicable - PATIENT died or stillbirth
			and
			apc.DischargeMethodHospitalProviderSpell != '5' -- not stillbirth
		)
)
group by apc.NHSNumber
	

Comment or raise an issue for this mapping.