death_date

SUS Outpatient Death

Source column death_date. Converts text to dates.

• death_date Discharge date of the patient’s spell. DISCHARGE DATE (HOSPITAL PROVIDER SPELL)

	select
		NHSNumber as nhs_number,
		coalesce(max(ReferralToTreatmentPeriodEndDate), max(CDSActivityDate)) as death_date
	from omop_staging.sus_OP
	where ReferralToTreatmentPeriodStatus = 36
		and (CDSActivityDate is not null or ReferralToTreatmentPeriodEndDate is not null)
		and NHSNumber is not null
	group by NHSNumber
	

Comment or raise an issue for this mapping.

SUS Inpatient Death

Source column death_date. Converts text to dates.

• death_date Discharge date of the patient’s spell. DISCHARGE DATE (HOSPITAL PROVIDER SPELL)

;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"

group by apc.NHSNumber
	

Comment or raise an issue for this mapping.

SUS A&E Death

Source column death_date. Converts text to dates.

• death_date Discharge date of the patient’s spell. DISCHARGE DATE (HOSPITAL PROVIDER SPELL)

select
	NHSNumber as nhs_number,
	coalesce(max(ReferralToTreatmentPeriodEndDate), max(CDSActivityDate)) as death_date
from omop_staging.sus_AE
where ((ReferralToTreatmentPeriodStatus = 36) --PATIENT died before treatment
	or (AEPatientGroup = 70) -- PATIENT brought in dead
	or (AEAttendanceDisposal = 10))  --PATIENT died in AE
	and (CDSActivityDate is not null or ReferralToTreatmentPeriodEndDate is not null)
	and NHSNumber is not null
group by NHSNumber
	

Comment or raise an issue for this mapping.

Oxford Spine Death

Source column DECEASED_DT_TM. Converts text to dates.

• DECEASED_DT_TM Spine datetime of death.

select
	patient_identifier_value,
	DECEASED_DT_TM
from ##duckdb_source##
	

Comment or raise an issue for this mapping.

Oxford GP Death

Source column DateofDeath. Converts text to dates.

• DateofDeath Date of death.

select
	distinct
		NHSNumber,
		DateofDeath
from omop_staging.oxford_gp_demographic
where DateofDeath is not null
order by
	NHSNumber,
	DateofDeath
	

Comment or raise an issue for this mapping.

COSD v9 DeathDischargeDestination

• Value copied from DeathDate

• DeathDate The date on which a PERSON died or is officially deemed to have died. DISCHARGE DATE (HOSPITAL PROVIDER SPELL), TREATMENT START DATE (CANCER)

select distinct
    Record ->> '$.LinkagePatientId.NhsNumber.@extension' as NhsNumber,
    coalesce(
        cast(Record ->> '$.Treatment.DischargeDateHospitalProviderSpell' as date),
        make_date(
            cast(extract(year from cast(Record ->> '$.Treatment.TreatmentStartDateCancer' as date)) as integer),
            12,
            31
        )
    ) as DeathDate
from omop_staging.cosd_staging_901
where (Record ->> '$.Treatment.DischargeDestinationHospitalProviderSpell.@code') = '79';-- Not applicable - PATIENT died or stillbirth
	

Comment or raise an issue for this mapping.

COSD v9 BasisOfDiagnosisCancer

• Value copied from DeathDate

• DeathDate The date on which a PERSON died or is officially deemed to have died. MULTIDISCIPLINARY TEAM DISCUSSION DATE (CANCER), TREATMENT START DATE (CANCER), TNM STAGE GROUPING DATE (FINAL PRETREATMENT), DATE OF PRIMARY CANCER DIAGNOSIS (CLINICALLY AGREED)

	    with cosddates as (
    select
        Record ->> '$.LinkagePatientId.NhsNumber.@extension' as NhsNumber,
        cast(Record ->> '$.Treatment.TreatmentStartDateCancer' as date) as TreatmentStartDateCancer,
        cast(Record ->> '$.CancerCarePlan.MultidisciplinaryTeamDiscussionDateCancer' as date) as MultidisciplinaryTeamDiscussionDateCancer,
        cast(Record ->> '$.PrimaryPathway.Staging.StageDateFinalPretreatmentStage' as date) as StageDateFinalPretreatmentStage,
        cast(Record ->> '$.PrimaryPathway.LinkageDiagnosticDetails.DateOfPrimaryDiagnosisClinicallyAgreed' as date) as DateOfPrimaryDiagnosisClinicallyAgreed
    from omop_staging.cosd_staging_901
    where type = 'CO'
      and (Record ->> '$.PrimaryPathway.Diagnosis.BasisOfDiagnosisCancer.@code') in ('0', '1')
), dates as (
    select NhsNumber, TreatmentStartDateCancer as "Date" from cosddates where TreatmentStartDateCancer is not null
    union
    select NhsNumber, MultidisciplinaryTeamDiscussionDateCancer as "Date" from cosddates where MultidisciplinaryTeamDiscussionDateCancer is not null
    union
    select NhsNumber, StageDateFinalPretreatmentStage as "Date" from cosddates where StageDateFinalPretreatmentStage is not null
    union
    select NhsNumber, DateOfPrimaryDiagnosisClinicallyAgreed as "Date" from cosddates where DateOfPrimaryDiagnosisClinicallyAgreed is not null
)
select
    NhsNumber,
    make_date(cast(extract(year from max("Date")) as integer), 12, 31) as DeathDate
from dates
group by NhsNumber;
	

Comment or raise an issue for this mapping.

COSD v8 Death

Source column DeathDate. Converts text to dates.

• DeathDate The date on which a PERSON died or is officially deemed to have died. PERSON DEATH DATE

select 
	distinct
		Record ->> '$..NHSNumber..@extension' ->> 0 as NhsNumber,
  		Record ->> '$..PersonDeathDate' ->> 0 as DeathDate
from omop_staging.cosd_staging_81
where DeathDate is not null

Comment or raise an issue for this mapping.