nhs_number

SUS Outpatient Location

• Value copied from NHSNumber

• NHSNumber Patient NHS Number NHS NUMBER

	select
		distinct
			Postcode,
			Country,
			NHSNumber
	from [omop_staging].[sus_OP]
	where NHSNumber is not null
		and Postcode is not null
		and AttendedorDidNotAttend in ('5','6')
	

Comment or raise an issue for this mapping.

SUS Inpatient Location

• Value copied from NHSNumber

• NHSNumber Patient NHS Number NHS NUMBER

select
	distinct
		Postcode,
		NHSNumber
from omop_staging.sus_APC
where Postcode is not null;
	

Comment or raise an issue for this mapping.

SUS A&E Location

• Value copied from NHSNumber

• NHSNumber Patient NHS Number NHS NUMBER

select
	distinct
		Postcode,
		NHSNumber
from omop_staging.sus_AE
where Postcode is not null
and NHSNumber is not null
	

Comment or raise an issue for this mapping.

SACT Location

• Value copied from NHS_Number

• NHS_Number Patient NHS Number NHS NUMBER

select
	NHS_Number,
	max (Patient_Postcode) as Patient_Postcode,
	max (Date_Of_Birth) as Date_Of_Birth
from omop_staging.sact_staging
group by NHS_Number
	

Comment or raise an issue for this mapping.

Rtds PAS Location

• Value copied from FirstOfNHSNUMBER

• FirstOfNHSNUMBER Patient NHS Number NHS NUMBER

select
	distinct
		p.FirstOfNHSNUMBER,
		p.FirstOfPOSTCODE
from omop_staging.RTDS_PASDATA p
where p.FirstOfPOSTCODE is not null
	and p.FirstOfNHSNUMBER is not null;
	

Comment or raise an issue for this mapping.

Oxford GP Location

• Value copied from NHSNumber

• NHSNumber Patient NHS Number NHS NUMBER

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

Comment or raise an issue for this mapping.

COSD Demographics

• Value copied from NhsNumber

• NhsNumber Patient NHS Number NHS NUMBER

with 
	XMLNAMESPACES('http://www.datadictionary.nhs.uk/messages/COSD-v8-1' AS COSD81, '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('*[local-name() != "Id"][1]/*[1]') as Node, -- Select the first inner element of the element that is not called Id.
		convert(bit, 1) as Is81
	from omop_staging.cosd_staging
	cross apply content.nodes('COSD81:COSD/*') as T(staging)
	where T.staging.exist('Id/@root') = 1
	union all
	select
		T.staging.value('(Id/@root)[1]', 'uniqueidentifier') as Id,
		T.staging.query('.') as Node,
		convert(bit, 0) as Is81
	from omop_staging.cosd_staging
	cross apply content.nodes('COSD901:COSD/*') as T(staging)
	where T.staging.exist('Id/@root') = 1
), COSDElements as (
	select
		Id,
		Node.query('(*[1]/*[fn:contains (fn:local-name(.), "LinkagePatientId")])[1]') as LinkagePatient,
		Node.query('(*[1]/*[fn:contains (fn:local-name(.), "Demographics")])[1]') as Demographics,
		Is81
	from CosdRecords
), Patients as (
	select
		LinkagePatient.value('(*/*[local-name() = "NHSNumber" or local-name() = "NhsNumber"]/@extension)[1]', 'varchar(max)') as NhsNumber,
		LinkagePatient.value('(*/*[local-name() = "PersonBirthDate" or local-name() = "Birthdate"])[1]', 'varchar(max)') as DateOfBirth,
		Demographics.value('(*/EthnicCategory/@code)[1]', 'varchar(max)') as EthnicCategory,
		Demographics.value('(*/Address/StructuredAddress/*[local-name() = "StreetAddressLine" or local-name() = "streetAddressLine"][1]/text())[1]', 'VARCHAR(255)') as StreetAddressLine1,
		Demographics.value('(*/Address/StructuredAddress/*[local-name() = "StreetAddressLine" or local-name() = "streetAddressLine"][2]/text())[1]', 'VARCHAR(255)') as StreetAddressLine2,
		Demographics.value('(*/Address/StructuredAddress/*[local-name() = "StreetAddressLine" or local-name() = "streetAddressLine"][3]/text())[1]', 'VARCHAR(255)') as StreetAddressLine3,
		Demographics.value('(*/Address/StructuredAddress/*[local-name() = "StreetAddressLine" or local-name() = "streetAddressLine"][4]/text())[1]', 'VARCHAR(255)') as StreetAddressLine4,
		case when Is81 = 1 then Demographics.value('(*/Postcode/postalCode)[1]', 'varchar(max)') else Demographics.value('(*/PostcodeOfUsualAddressAtDiagnosis/text())[1]', 'VARCHAR(10)') end as Postcode
	from COSDElements
)
select
	NhsNumber,
	max (DateOfBirth) as DateOfBirth,
	max (EthnicCategory) as EthnicCategory,
	max (StreetAddressLine1) as StreetAddressLine1,
	max (StreetAddressLine2) as StreetAddressLine2,
	max (StreetAddressLine3) as StreetAddressLine3,
	max (StreetAddressLine4) as StreetAddressLine4,
	max (Postcode) as Postcode
from Patients 
where NhsNumber != ''
group by NhsNumber
	

Comment or raise an issue for this mapping.