NhsNumber

COSD v9 DeathDischargeDestination

• Value copied from NhsNumber

• NhsNumber Patient NHS Number NHS NUMBER

;with 
	XMLNAMESPACES('http://www.datadictionary.nhs.uk/messages/COSD-v9-0-1' AS COSD901),
	CosdRecords as ( 

	select
		T.staging.value('(Id/@root)[1]', 'uniqueidentifier') as Id,
		T.staging.query('.') as Node
	from omop_staging.cosd_staging
	cross apply content.nodes('COSD901:COSD/*') as T(staging)
	where T.staging.exist('Id/@root') = 1
		and Content.value('namespace-uri((/*:COSD)[1])','nvarchar(max)') = 'http://www.datadictionary.nhs.uk/messages/COSD-v9-0-1'
)
select
	distinct
		Node.value('(*/LinkagePatientId/NhsNumber/@extension)[1]', 'varchar(max)') as NhsNumber,
		coalesce
		(
			Node.value('(/*/Treatment/DischargeDateHospitalProviderSpell)[1]', 'varchar(max)'),
			datefromparts
			(
				year(convert(datetime, Node.value('(/*/Treatment/TreatmentStartDateCancer)[1]', 'varchar(max)'))),
				12,
				31
			)
		) as DeathDate
from CosdRecords
where Node.value('(/*/Treatment/DischargeDestinationHospitalProviderSpell/@code)[1]', 'varchar(max)') = 79 -- Not applicable - PATIENT died or stillbirth
	

Comment or raise an issue for this mapping.

COSD v9 BasisOfDiagnosisCancer

• Value copied from NhsNumber

• NhsNumber Patient NHS Number NHS NUMBER

;with 
	XMLNAMESPACES('http://www.datadictionary.nhs.uk/messages/COSD-v9-0-1' AS COSD901),
	CosdRecords as ( 

	select
		T.staging.value('(Id/@root)[1]', 'uniqueidentifier') as Id,
		T.staging.query('.') as Node
	from omop_staging.cosd_staging
	cross apply content.nodes('COSD901:COSD/*') as T(staging)
	where T.staging.exist('Id/@root') = 1
		and Content.value('namespace-uri((/*:COSD)[1])','nvarchar(max)') = 'http://www.datadictionary.nhs.uk/messages/COSD-v9-0-1'
), CosdDates as (
	select 
		convert(varchar(max), Node.value('(*/LinkagePatientId/NhsNumber/@extension)[1]', 'varchar(max)')) as NhsNumber,
		convert(datetime, Node.value('(/*/Treatment/TreatmentStartDateCancer)[1]', 'varchar(max)')) as TreatmentStartDateCancer,
		convert(datetime, Node.value('(/*/CancerCarePlan/MultidisciplinaryTeamDiscussionDateCancer)[1]', 'varchar(max)')) as MultidisciplinaryTeamDiscussionDateCancer,
		convert(datetime, Node.value('(/*/PrimaryPathway/Staging/StageDateFinalPretreatmentStage)[1]', 'varchar(max)')) as StageDateFinalPretreatmentStage,
		convert(datetime, Node.value('(/*/PrimaryPathway/LinkageDiagnosticDetails/DateOfPrimaryDiagnosisClinicallyAgreed)[1]', 'varchar(max)')) as DateOfPrimaryDiagnosisClinicallyAgreed
	from CosdRecords
	where Node.value('(//BasisOfDiagnosisCancer/@code)[1]', 'int') in (0, 1)
), Dates as (
	select
		NhsNumber,
		TreatmentStartDateCancer as [Date]
	from CosdDates

	union 

	select
		NhsNumber,
		MultidisciplinaryTeamDiscussionDateCancer as [Date]
	from CosdDates

	union 

	select
		NhsNumber,
		StageDateFinalPretreatmentStage as [Date]
	from CosdDates

	union 

	select
		NhsNumber,
		DateOfPrimaryDiagnosisClinicallyAgreed as [Date]
	from CosdDates
)
select
	NhsNumber,
	datefromparts(year(max ([Date])), 12, 31) as DeathDate
from Dates
group by NhsNumber
	

Comment or raise an issue for this mapping.

COSD v8 Death

• Value copied from NhsNumber

• NhsNumber Patient NHS Number NHS NUMBER

;with XMLNAMESPACES('http://www.datadictionary.nhs.uk/messages/COSD-v8-1' AS COSD),
	CosdRecords as ( 
	select
		T.staging.value('(Id/@root)[1]', 'uniqueidentifier') as Id,
		T.staging.query('*[local-name() != "Id"][1]/*[1]') as Node -- Select the first inner element of the element that is not called Id.
	from omop_staging.cosd_staging
	cross apply content.nodes('COSD:COSD/*') as T(staging)
	where T.staging.exist('Id/@root') = 1
		and Content.value('namespace-uri((/*:COSD)[1])','nvarchar(max)') = 'http://www.datadictionary.nhs.uk/messages/COSD-v8-1'
		--and substring (FileName, 15, 2) = 'CO'
)
select 
	distinct
		Node.value('(//NHSNumber/@extension)[1]', 'varchar(max)') as NhsNumber,
		Node.value('(//PersonDeathDate)[1]', 'varchar(max)') as DeathDate
from CosdRecords
where Node.value('(//PersonDeathDate)[1]', 'varchar(max)') is not null;
	

Comment or raise an issue for this mapping.

CDS Death

• Value copied from nhs_number

• nhs_number Patient NHS Number NHS NUMBER

select
	distinct	
		l1.NHSNumber as nhs_number,
		l5.DischargeDateHospitalProviderSpell as death_date
from omop_staging.cds_line01 l1
	inner join omop_staging.cds_line05 l5
		on l1.MessageId = l5.MessageId
where l1.NHSNumber is not null
	and 
	(
		l5.DischargeMethod = '4' -- "Patient died"
		or 
		(
			l5.DischargeDestinationCode = '79' and -- Not applicable - PATIENT died or stillbirth
			l5.DischargeMethod != '5' -- not stillbirth
		)
	);
	

Comment or raise an issue for this mapping.