RecordConnectionIdentifier

SUS OP Source Of Referral For Outpatients

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
	NHSNumber,
	GeneratedRecordIdentifier,
	AppointmentDate,
	AppointmentTime,
	ReferrerCode   -- Referrer code is the code of the person making the referral request
from [omop_staging].[sus_OP]
	where ReferrerCode is not null
	and NHSNumber is not null
	and AttendedorDidNotAttend in ('5','6')
	

Comment or raise an issue for this mapping.

SUS OP Referral Received Date For Outpatients

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

	select
		op.NHSNumber, 
		op.AppointmentDate,
		op.AppointmentTime,
		op.ReferralRequestReceivedDate,
		op.GeneratedRecordIdentifier
	from omop_staging.sus_OP op
	where ReferralRequestReceivedDate is not null
		and op.NHSNumber is not null
		and AttendedorDidNotAttend in ('5','6')
	

Comment or raise an issue for this mapping.

SUS Outpatient Carer Support Indicator Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select 
	op.NHSNumber, 
	max(op.CDSActivityDate) as CDSActivityDate, 
	op.CarerSupportIndicator,
	op.GeneratedRecordIdentifier
from omop_staging.sus_OP op
where op.CarerSupportIndicator is not null
	and op.NHSNumber is not null
	and AttendedorDidNotAttend in ('5','6')
group by
	op.NHSNumber, 
	op.CarerSupportIndicator,
	op.GeneratedRecordIdentifier;
	

Comment or raise an issue for this mapping.

SUS Inpatient Total Previous Pregnancies Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select 
	apc.NHSNumber, 
	apc.GeneratedRecordIdentifier,
	apc.HospitalProviderSpellNumber,
	max(apc.CDSActivityDate) as observation_date,
	apc.PregnancyTotalPreviousPregnancies
from [omop_staging].[sus_APC] apc
where apc.NHSNumber is not null
	and apc.PregnancyTotalPreviousPregnancies is not null
	and apc.CDSActivityDate is not null
	and apc.CdsType in ('140', '120')
group by 
	apc.NHSNumber, 
	apc.GeneratedRecordIdentifier, 
    apc.HospitalProviderSpellNumber,
	apc.PregnancyTotalPreviousPregnancies;
	

Comment or raise an issue for this mapping.

SUS APC Source Of Referral For Inpatients

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select	
	NHSNumber,
	GeneratedRecordIdentifier,
	StartDateHospitalProviderSpell,
	StartTimeHospitalProviderSpell,
	ReferrerCode   -- Referrer code is the code of the person making the referral request
FROM [omop_staging].[sus_APC]
where NHSNumber is not null
	

Comment or raise an issue for this mapping.

SUS APC Referral Received Date For Inpatients

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

	select
		apc.NHSNumber, 
		apc.StartDateHospitalProviderSpell,
		apc.StartTimeHospitalProviderSpell,
		apc.ReferralToTreatmentPeriodStartDate,
		apc.GeneratedRecordIdentifier
	from omop_staging.sus_APC apc
	where ReferralToTreatmentPeriodStartDate is not null
		and apc.NHSNumber is not null
	

Comment or raise an issue for this mapping.

SUS Inpatient NumberofBabies Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
	apc.NHSNumber,
	apc.GeneratedRecordIdentifier,
	apc.HospitalProviderSpellNumber,
	coalesce(max(apc.DeliveryDate), max(apc.CDSActivityDate)) as observation_date,
	apc.NumberofBabies
from [omop_staging].[sus_APC] apc													
where apc.NHSNumber is not null
	and apc.NumberofBabies is not null
	and apc.CDSType in ('120','140')
group by
	apc.NHSNumber,
	apc.GeneratedRecordIdentifier, 
    apc.HospitalProviderSpellNumber,
	apc.DeliveryDate,
	apc.NumberofBabies;
	

Comment or raise an issue for this mapping.

SUS Inpatient Gestation Length Labour Onset Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select 
	apc.NHSNumber, 
	apc.GeneratedRecordIdentifier,
	apc.HospitalProviderSpellNumber,
	coalesce(max(apc.DeliveryDate), max(apc.CDSActivityDate)) as observation_date, 
	apc.GestationLengthLabourOnset
from [omop_staging].[sus_APC] as apc																			
where apc.NHSNumber is not null
  and apc.GestationLengthLabourOnset is not null
  and apc.CDSType in ('120', '140')
group by 
	apc.NHSNumber, 
	apc.GeneratedRecordIdentifier, 
    apc.HospitalProviderSpellNumber,
	apc.DeliveryDate, 
	apc.GestationLengthLabourOnset;
	

Comment or raise an issue for this mapping.

SUS Inpatient Carer Support Indicator Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select 
	apc.NHSNumber, 
	max(apc.CDSActivityDate) as CDSActivityDate, 
	apc.CarerSupportIndicator,
	apc.HospitalProviderSpellNumber,
	apc.GeneratedRecordIdentifier
from omop_staging.sus_APC apc
where apc.CarerSupportIndicator is not null
	and apc.NHSNumber is not null
group by
	apc.NHSNumber, 
	apc.CarerSupportIndicator,
	apc.HospitalProviderSpellNumber,
	apc.GeneratedRecordIdentifier;
	

Comment or raise an issue for this mapping.

Sus APC Birth Weight Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select 
	apc.NHSNumber, 
	apc.GeneratedRecordIdentifier,
	apc.HospitalProviderSpellNumber,
	coalesce(max(apc.DeliveryDate), max(apc.CDSActivityDate)) as observation_date, 
	b.BirthWeightBaby as BirthWeight
from [omop_staging].[sus_APC] apc
	inner join [omop_staging].[sus_Birth] as b
		on apc.MessageId = b.MessageId
where b.BirthWeightBaby is not null
  and apc.NHSNumber is not null
  and apc.CdsType in ('140', '120')
group by 
	apc.NHSNumber,
	apc.GeneratedRecordIdentifier,
    apc.HospitalProviderSpellNumber,
	apc.DeliveryDate,
	b.BirthWeightBaby;

Comment or raise an issue for this mapping.

SUS APC Anaesthetic Given Post Labour Delivery Observation

  • Value copied from RecordConnectionIdentifier

  • RecordConnectionIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
    apc.NHSNumber, 
    apc.GeneratedRecordIdentifier, 
	apc.HospitalProviderSpellNumber,
    coalesce(max(apc.DeliveryDate), max(apc.CDSActivityDate)) as observation_date, 
    apc.AnaestheticGivenPostDelivery
from omop_staging.sus_APC as apc
where apc.AnaestheticGivenPostDelivery is not null
  and apc.NHSNumber is not null
  and apc.CdsType in ('140', '120')
group by 
    apc.NHSNumber, 
    apc.GeneratedRecordIdentifier,
	apc.HospitalProviderSpellNumber,
    apc.DeliveryDate,
    apc.AnaestheticGivenPostDelivery;

Comment or raise an issue for this mapping.

SUS APC Anaesthetic During Labour Delivery Observation

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
    apc.NHSNumber, 
    apc.GeneratedRecordIdentifier, 
    coalesce(max(apc.DeliveryDate), max(apc.CDSActivityDate)) as observation_date,
	apc.HospitalProviderSpellNumber,
    apc.AnaestheticGivenDuringLabourDelivery
from omop_staging.sus_APC as apc
where apc.AnaestheticGivenDuringLabourDelivery is not null
  and apc.NHSNumber is not null
  and apc.CdsType in ('140', '120')
group by 
    apc.NHSNumber, 
    apc.GeneratedRecordIdentifier,
	apc.HospitalProviderSpellNumber,
    apc.DeliveryDate, 
    apc.AnaestheticGivenDuringLabourDelivery;
	

Comment or raise an issue for this mapping.

SUS AE Source Of Referral For AE

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
	NHSNumber,
	GeneratedRecordIdentifier,
    ArrivalDate,
    ArrivalTime,
	SourceofReferralForAE   -- Referrer code is the code of the person making the referral request
from omop_staging.sus_AE
where SourceofReferralForAE is not null
	

Comment or raise an issue for this mapping.

SUS AE Diabetic Patient

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
	distinct
		d.AccidentAndEmergencyDiagnosis,
		ae.GeneratedRecordIdentifier,
		ae.NHSNumber,
		ae.ArrivalDate,
		ae.ArrivalTime
from omop_staging.sus_AE_diagnosis d
	inner join omop_staging.sus_AE ae
		on d.MessageId = ae.MessageId
where ae.NHSNumber is not null
and d.AccidentAndEmergencyDiagnosis in ('30','301')
	

Comment or raise an issue for this mapping.

SUS AE Diabetic Patient

  • Value copied from GeneratedRecordIdentifier

  • GeneratedRecordIdentifier CDS specific identifier that binds multiple CDS messages together. CDS RECORD IDENTIFIER

select
	distinct
		d.AccidentAndEmergencyDiagnosis,
		ae.GeneratedRecordIdentifier,
		ae.NHSNumber,
		ae.ArrivalDate,
		ae.ArrivalTime
from omop_staging.sus_AE_diagnosis d
	inner join omop_staging.sus_AE ae
		on d.MessageId = ae.MessageId
where ae.NHSNumber is not null
and d.AccidentAndEmergencyDiagnosis in ('20','201')
	

Comment or raise an issue for this mapping.