person_source_value

SUS Outpatient Person

  • Value copied from NHSNumber

  • NHSNumber Patient NHS Number NHS NUMBER

	select
		NHSNumber,
		max(DateofBirth) as DateOfBirth,
		max(EthnicCategory) as EthnicCategory,
		max(Sex) as PersonCurrentGenderCode
	from omop_staging.sus_OP
	where NHSNumber is not null
	group by NHSNumber

Comment or raise an issue for this mapping.

SUS Inpatient Person

  • Value copied from NHSNumber

  • NHSNumber Patient NHS Number NHS NUMBER

	select
		NHSNumber,
		max(DateofBirth) as DateOfBirth,
		max(EthnicGroup) as EthnicCategory,
		max(Sex) as PersonCurrentGenderCode
	from omop_staging.sus_APC
	where NHSNumber is not null
	group by NHSNumber

Comment or raise an issue for this mapping.

SUS A&E Person

  • Value copied from NHSNumber

  • NHSNumber Patient NHS Number NHS NUMBER

	select
		NHSNumber,
		max(DateofBirth) as DateOfBirth,
		max(EthnicCategory) as EthnicCategory,
		max(Sex) as PersonCurrentGenderCode
	from omop_staging.sus_AE
	where NHSNumber is not null
	group by NHSNumber

Comment or raise an issue for this mapping.

SACT Person

  • Value copied from NHS_Number

  • NHS_Number Patient NHS Number NHS NUMBER

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

Comment or raise an issue for this mapping.

Rtds Person

  • Value copied from PatientId

  • PatientId Patient NHS Number NHS NUMBER

select
	PatientId,
	max (DateOfBirth) as DateOfBirth,
	max (Sex) as Sex
from omop_staging.RTDS_1_Demographics d
where PatientId is not null
group by PatientId

Comment or raise an issue for this mapping.

Oxford GP Person

  • Value copied from NHSNumber

  • NHSNumber Patient NHS Number NHS NUMBER

select
	distinct
		NHSNumber,
		DOB
from omop_staging.oxford_gp_demographic
order by
	NHSNumber,
	DOB

Comment or raise an issue for this mapping.

COSD Demographics

  • Value copied from NhsNumber

  • NhsNumber Patient NHS Number NHS NUMBER

with demographics as (
select 
  Record ->> '$..NhsNumber..@extension' ->> 0 as NhsNumber,
  Record ->> '$..PersonBirthDate' ->> 0 as DateOfBirth,
  Record ->> '$..StreetAddressLine[0].#cdata-section' ->> 0 as StreetAddressLine1,
  Record ->> '$..StreetAddressLine[1].#cdata-section' ->> 0 as StreetAddressLine2,
  Record ->> '$..StreetAddressLine[2].#cdata-section' ->> 0 as StreetAddressLine3,
  Record ->> '$..StreetAddressLine[3].#cdata-section' ->> 0 as StreetAddressLine4,
  Record ->> '$..PostcodeOfUsualAddressAtDiagnosis' ->> 0 as Postcode,
  Record ->> '$..EthnicCategory.@code' ->> 0 as EthnicCategory
from omop_staging.cosd_staging_901
)
select
	NhsNumber,
	max (DateOfBirth) as DateOfBirth,
	max (StreetAddressLine1) as StreetAddressLine1,
	max (StreetAddressLine2) as StreetAddressLine2,
	max (StreetAddressLine3) as StreetAddressLine3,
	max (StreetAddressLine4) as StreetAddressLine4,
	max (Postcode) as Postcode
from demographics 
where NhsNumber is not null
group by NhsNumber

Comment or raise an issue for this mapping.

COSD Demographics v8

  • Value copied from NhsNumber

  • NhsNumber Patient NHS Number NHS NUMBER

with demographics as (
select 
  Record ->> '$..NHSNumber..@extension' ->> 0 as NhsNumber,
  Record ->> '$..Birthdate' ->> 0 as DateOfBirth,
  Record ->> '$..streetAddressLine[0].#cdata-section' ->> 0 as StreetAddressLine1,
  Record ->> '$..streetAddressLine[1].#cdata-section' ->> 0 as StreetAddressLine2,
  Record ->> '$..streetAddressLine[2].#cdata-section' ->> 0 as StreetAddressLine3,
  Record ->> '$..streetAddressLine[3].#cdata-section' ->> 0 as StreetAddressLine4,
  Record ->> '$..Postcode.postalCode' -> 0 as Postcode,
from omop_staging.cosd_staging_81
)
select
	NhsNumber,
	max (DateOfBirth) as DateOfBirth,
	max (StreetAddressLine1) as StreetAddressLine1,
	max (StreetAddressLine2) as StreetAddressLine2,
	max (StreetAddressLine3) as StreetAddressLine3,
	max (StreetAddressLine4) as StreetAddressLine4,
	max (Postcode) as Postcode
from demographics 
where NhsNumber is not null
group by NhsNumber

Comment or raise an issue for this mapping.