procedure_end_date

SUS Outpatient Procedure Occurrence

Source column AppointmentDate. Converts text to dates.

• AppointmentDate Appointment Date. APPOINTMENT DATE

	select
		distinct
		op.GeneratedRecordIdentifier,
		op.NHSNumber,
		op.AppointmentDate,
		op.AppointmentTime,
		p.ProcedureOPCS as PrimaryProcedure
	from omop_staging.sus_OP op
		inner join omop_staging.sus_OP_OPCSProcedure p
		on op.MessageId = p.MessageId
	where NHSNumber is not null
		and AttendedorDidNotAttend in ('5','6')
	

Comment or raise an issue for this mapping.

SUS CCMDS Procedure Occurrence

Source column ProcedureOccurrenceEndDate. Converts text to dates.

• ProcedureOccurrenceEndDate End date of the Procedure, if exists, else the event date CRITICAL CARE PERIOD DISCHARGE DATE, EVENT DATE

		select distinct
				apc.NHSNumber,
				apc.GeneratedRecordIdentifier,
				cc.CriticalCareStartDate as ProcedureOccurrenceStartDate,
				coalesce(cc.CriticalCareStartTime, '00:00:00') as ProcedureOccurrenceStartTime,
				coalesce(cc.CriticalCarePeriodDischargeDate, cc.EventDate) as ProcedureOccurrenceEndDate,
				coalesce(cc.CriticalCarePeriodDischargeTime, '00:00:00') as ProcedureOccurrenceEndTime,
				d.CriticalCareActivityCode as ProcedureSourceValue
		from omop_staging.sus_CCMDS_CriticalCareActivityCode d
		inner join omop_staging.sus_CCMDS cc on d.MessageId = cc.MessageId
		inner join omop_staging.sus_APC apc on cc.GeneratedRecordID = apc.GeneratedRecordIdentifier
		where apc.NHSNumber is not null
		and d.CriticalCareActivityCode != '99'  -- No Defined Critical Care Activity

	

Comment or raise an issue for this mapping.

SUS APC Procedure Occurrence

Source column PrimaryProcedureDate. Converts text to dates.

• PrimaryProcedureDate Procedure Date. PROCEDURE DATE

select
	distinct
		apc.GeneratedRecordIdentifier,
		apc.NHSNumber,
		p.ProcedureDateOPCS as PrimaryProcedureDate,
		p.ProcedureOPCS as PrimaryProcedure
from omop_staging.sus_APC apc
	inner join omop_staging.sus_OPCSProcedure p
		on apc.MessageId = p.MessageId
where NHSNumber is not null
	

Comment or raise an issue for this mapping.

SUS AE Procedure Occurrence

Source column PrimaryProcedureDate. Converts text to dates.

• PrimaryProcedureDate Procedure Date. PROCEDURE DATE

	select
		distinct
			ae.GeneratedRecordIdentifier,
			ae.NHSNumber,
			ae.CDSActivityDate as PrimaryProcedureDate,
			p.AccidentAndEmergencyTreatment as PrimaryProcedure
	from omop_staging.sus_AE ae
		inner join omop_staging.sus_AE_treatment p
			on AE.MessageId = p.MessageId
	where NHSNumber is not null
	

Comment or raise an issue for this mapping.

CDS Procedure Occurrence

Source column PrimaryProcedureDate. Converts text to dates.

• PrimaryProcedureDate Procedure Date. PROCEDURE DATE

select
	distinct
		l1.RecordConnectionIdentifier,
		l1.NHSNumber,
		p.PrimaryProcedureDate,
		p.PrimaryProcedure
from omop_staging.cds_line01 l1
	inner join omop_staging.cds_procedure p
		on l1.MessageId = p.MessageId
where l1.NHSNumber is not null;
	

Comment or raise an issue for this mapping.