visit_detail_end_date

Sus Outpatient VisitDetails

Source column VisitEndDate. Converts text to dates.

• VisitEndDate End date of the episode, if exists, else the spell discharge date, if exists, else the message date. CDS ACTIVITY DATE, END DATE (EPISODE), DISCHARGE DATE (HOSPITAL PROVIDER SPELL)

with results as
(
	select  
		distinct
			op.NHSNumber,
			op.SUSgeneratedspellID,
			coalesce(op.AppointmentDate, op.CDSActivityDate) as VisitStartDate,  -- visit_start_date
			coalesce(op.AppointmentTime, '000000') as VisitStartTime,  -- visit_start_time
			coalesce(op.AppointmentDate, op.CDSActivityDate) as VisitEndDate,
			null as VisitEndTime
	from omop_staging.sus_OP op
	where op.UpdateType = 9   -- New/Modification     (1 = Delete)
		and op.NHSNumber is not null
		and AttendedorDidNotAttend in ('5','6')
)
select *
from results
order by 
	NHSNumber,
	SUSgeneratedspellID,
	VisitStartDate, 
	VisitStartTime,
	VisitEndDate,
	VisitEndTime
		
	

Comment or raise an issue for this mapping.

Sus Critical Care VisitDetails

Source column VisitEndDate. Converts text to dates.

• VisitEndDate Discharge date of the critical care period, if exists, else the event date CRITICAL CARE PERIOD DISCHARGE DATE, EVENT DATE

with results as (
	select
		distinct
			apc.NHSNumber,
			apc.HospitalProviderSpellNumber,
			cc.CriticalCareStartDate as VisitStartDate,
			coalesce(cc.CriticalCareStartTime, '00:00:00') as VisitStartTime,
			coalesce(cc.CriticalCarePeriodDischargeDate, cc.EventDate) as VisitEndDate,
			coalesce(cc.CriticalCarePeriodDischargeTime, '00:00:00') as VisitEndTime
	from omop_staging.sus_CCMDS cc
	inner join omop_staging.sus_APC apc on cc.GeneratedRecordID = apc.GeneratedRecordIdentifier
	where apc.NHSNumber is not null
)
select *
from results
order by
	NHSNumber,
	HospitalProviderSpellNumber,
	VisitStartDate,
	VisitStartTime,
	VisitEndDate,
	VisitEndTime

	

Comment or raise an issue for this mapping.

Sus Inptatient VisitDetails

Source column VisitEndDate. Converts text to dates.

• VisitEndDate End date of the episode, if exists, else the spell discharge date, if exists, else the message date. CDS ACTIVITY DATE, END DATE (EPISODE), DISCHARGE DATE (HOSPITAL PROVIDER SPELL)

		with records as (
			select
				apc.NHSNumber,
				apc.HospitalProviderSpellNumber,
		
				coalesce(apc.StartDateConsultantEpisode, apc.StartDateHospitalProviderSpell, apc.CDSActivityDate) as VisitStartDate,
				coalesce(apc.StartTimeEpisode, apc.StartTimeHospitalProviderSpell, '000000') as VisitStartTime,
				coalesce(apc.EndDateConsultantEpisode, apc.DischargeDateFromHospitalProviderSpell, apc.CDSActivityDate) as VisitEndDate,
				coalesce(apc.EndTimeEpisode, apc.DischargeTimeHospitalProviderSpell, '000000') as VisitEndTime,
		
				apc.SourceOfAdmissionHospitalProviderSpell as SourceofAdmissionCode,
				apc.DischargeDestinationHospitalProviderSpell as DischargeDestinationCode
		
			from omop_staging.sus_APC apc
			where apc.NHSNumber is not null
		)
		select *
		from records
		order by 
			NHSNumber, 
			HospitalProviderSpellNumber, 
			VisitStartDate, 
			VisitStartTime, 
			VisitEndDate, 
			VisitEndTime, 
			SourceofAdmissionCode, 
			DischargeDestinationCode

	

Comment or raise an issue for this mapping.

Sus Inptatient VisitDetails

Source column VisitEndDate. Converts text to dates.

• VisitEndDate End date of the episode, if exists, else the spell discharge date, if exists, else the message date. CDS ACTIVITY DATE, END DATE (EPISODE), DISCHARGE DATE (HOSPITAL PROVIDER SPELL)

		with records as (
			select  
				ae.NHSNumber,
				ae.AEAttendanceNumber,
				coalesce(ae.ArrivalDate, ae.CDSActivityDate) as VisitStartDate,
				coalesce(ae.ArrivalTime, '000000') as VisitStartTime,
				coalesce(ae.AEDepartureDate, ae.AEAttendanceConclusionDate, ae.ArrivalDate, ae.CDSActivityDate) as VisitEndDate,
				coalesce(ae.AEDepartureTime, ae.AEAttendanceConclusionTime, '000000') as VisitEndTime,
				ae.AEArrivalMode as SourceofAdmissionCode,
				ae.AEAttendanceDisposal as DischargeDestinationCode
			from omop_staging.sus_AE ae
			where ae.NHSNumber is not null
		)
		select *
		from records
		order by 
			NHSNumber, 
			AEAttendanceNumber, 
			VisitStartDate, 
			VisitStartTime, 
			VisitEndDate, 
			VisitEndTime, 
			SourceofAdmissionCode, 
			DischargeDestinationCode

	

Comment or raise an issue for this mapping.

Oxford Visit Details

Source column EventDate. Converts text to dates.

• EventDate Event date

select
	GPEventsPrimaryKey,
	d.NHSNumber,
	e.EventDate
from omop_staging.oxford_gp_event e
	inner join omop_staging.oxford_gp_demographic d
		on e.PatientIdentifier = d.PatientIdentifier
order by
	d.NHSNumber,
	e.EventDate

Comment or raise an issue for this mapping.