nhs_number
SUS CCMDS Device Exposure
-
Value copied from
NHSNumber -
NHSNumberPatient NHS Number NHS NUMBER
select distinct
apc.NHSNumber,
apc.HospitalProviderSpellNumber,
cc.CriticalCareStartDate as DeviceExposureStartDate,
coalesce(cc.CriticalCareStartTime, '00:00:00') as DeviceExposureStartTime,
coalesce(cc.CriticalCarePeriodDischargeDate, cc.EventDate) as DeviceExposureEndDate,
coalesce(cc.CriticalCarePeriodDischargeTime, '00:00:00') as DeviceExposureEndTime,
d.CriticalCareActivityCode as CriticalCareActivityCode
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 not in('3','4','6','8','10','12','15','21','22','23','24','25','27','28','29','50','57','58','64','65','66','67','68','69','70','71','72','74','82','83','87','99')
Comment or raise an issue for this mapping.
SUS AE Device Exposure
-
Value copied from
NHSNumber -
NHSNumberPatient NHS Number NHS NUMBER
select
ae.AEAttendanceNumber,
ae.NHSNumber,
coalesce(ae.ArrivalDate, ae.CDSActivityDate) as StartDate,
coalesce(ae.ArrivalTime, '000000') as StartTime,
coalesce(ae.AEDepartureDate, ae.AEAttendanceConclusionDate) as EndDate,
coalesce(ae.AEDepartureTime, ae.AEAttendanceConclusionTime, '000000') as EndTime,
i.AccidentAndEmergencyInvestigation
from omop_staging.sus_AE_investigation i
inner join omop_staging.sus_AE ae
on i.MessageId = ae.MessageId
where ae.NHSNumber is not null
and i.AccidentAndEmergencyInvestigation in ('01', '02', '08', '09', '10', '11', '12', '19')