admitted_from_concept_id

Sus Inptatient VisitDetails

Source column SourceOfAdmissionHospitalProviderSpell. Lookup admission source concept.

SourceOfAdmissionHospitalProviderSpell admitted_from_concept_id notes
19 581476 Home Visit
29 8602 Temporary Lodging
37 38003619 Prison / Correctional Facility
40 38003619 Prison / Correctional Facility
42 38003619 Prison / Correctional Facility
49 38004284 Psychiatric Hospital
51 8717 Inpatient Hospital
52 8650 Birthing Center
53 8976 Psychiatric Residential Treatment Center
55 8863 Skilled Nursing Facility
56 38004306 Custodial Care Facility
66 38004205 Foster Care Agency
79 8650 Birthing Center
87 8717 Inpatient Hospital
88 8546 Hospice
98   No mapping possible
99   No mapping possible

Notes

;with RecordConnectionIdentifierNHSNumberCombination as (
	select
		distinct 
			apc.NHSNumber,
			apc.GeneratedRecordIdentifier
	from omop_staging.sus_APC apc
),

RecordsWithVariableNhsNumber as (
select
	m1.GeneratedRecordIdentifier
from RecordConnectionIdentifierNHSNumberCombination m1
	inner join RecordConnectionIdentifierNHSNumberCombination m2
		on m1.NHSNumber != m2.NHSNumber
where m1.GeneratedRecordIdentifier = m2.GeneratedRecordIdentifier
),

VisitDetail as (
	select  
		distinct
    
			apc.NHSNumber,
			apc.HospitalProviderSpellNumber,

			case 
				when apc.AdmissionMethodHospitalProviderSpell in ('21','24') and apc.PatientClassification = 1 then 262 
				when apc.AdmissionMethodHospitalProviderSpell in ('21','24') then 9203
				when apc.PatientClassification in (1) then 9201
				when apc.LocationClassAtEpisodeStartDate in ('02') then 581476
				else 9202
			end as VisitOccurrenceConceptId,    -- ""visit_concept_id""

			apc.GeneratedRecordIdentifier,

			coalesce(apc.StartDateHospitalProviderSpell, apc.CDSActivityDate, apc.StartDateConsultantEpisode) as VisitStartDate,  -- visit_start_date
			coalesce(apc.StartTimeEpisode, apc.StartTimeHospitalProviderSpell, '000000') as VisitStartTime,  -- visit_start_time

			coalesce(apc.DischargeDateFromHospitalProviderSpell, apc.CDSActivityDate, apc.EndDateConsultantEpisode) as VisitEndDate,

			coalesce(apc.EndTimeEpisode, apc.DischargeTimeHospitalProviderSpell, '000000') as VisitEndTime,

			case 
				when apc.CDSActivityDate is null and apc.DischargeDateFromHospitalProviderSpell is null and apc.StartDateConsultantEpisode is null and apc.PatientClassification = 1 then 32220
				else 32818
			end as VisitTypeConceptId,

			case 
				when apc.DischargeDateFromHospitalProviderSpell is null and apc.PatientClassification = 1 then 2
				else 1
			end as RowPriority,

			apc.SourceOfAdmissionHospitalProviderSpell,
			apc.DischargeDestinationHospitalProviderSpell
	from omop_staging.sus_APC apc
		inner join dbo.Code c 
			on apc.TreatmentFunctionCode = c.Code
	where apc.UpdateType = 9   -- New/Modification     (1 = Delete)
		and apc.NHSNumber is not null
		and c.CodeTypeId = 2 -- activity_treatment_function_code
		and not exists (select * from RecordsWithVariableNhsNumber rwvnn where rwvnn.GeneratedRecordIdentifier = apc.GeneratedRecordIdentifier)
), VisitDetailWithRank as (
	select
		*,
		row_number() over (partition by GeneratedRecordIdentifier order by RowPriority asc) as RowRank
	from VisitDetail
)
select
	*
from VisitDetailWithRank
where RowRank = 1

		
	

Comment or raise an issue for this mapping.

CDS VisitDetails

Source column SourceofAdmissionCode. Lookup admission source concept.

SourceofAdmissionCode admitted_from_concept_id notes
19 581476 Home Visit
29 8602 Temporary Lodging
37 38003619 Prison / Correctional Facility
40 38003619 Prison / Correctional Facility
42 38003619 Prison / Correctional Facility
49 38004284 Psychiatric Hospital
51 8717 Inpatient Hospital
52 8650 Birthing Center
53 8976 Psychiatric Residential Treatment Center
55 8863 Skilled Nursing Facility
56 38004306 Custodial Care Facility
66 38004205 Foster Care Agency
79 8650 Birthing Center
87 8717 Inpatient Hospital
88 8546 Hospice
98   No mapping possible
99   No mapping possible

Notes

;with RecordConnectionIdentifierNHSNumberCombination as (
	select
		distinct 
			l1.NHSNumber,
			l1.RecordConnectionIdentifier
	from omop_staging.cds_line01 l1
), RecordsWithVariableNhsNumber as (
select
	m1.RecordConnectionIdentifier
from RecordConnectionIdentifierNHSNumberCombination m1
	inner join RecordConnectionIdentifierNHSNumberCombination m2
		on m1.NHSNumber != m2.NHSNumber
where m1.RecordConnectionIdentifier = m2.RecordConnectionIdentifier
), VisitDetail as (
	select  
		distinct
    
			l1.NHSNumber,
			l5.HospitalProviderSpellNumber,

			case 
				when l5.AdmissionMethodCode in ('21','24') and l5.PatientClassification = 1 then 262 
				when l5.AdmissionMethodCode in ('21','24') then 9203
				when l5.PatientClassification in (1) then 9201
				when l4.LocationClass in ('02') then 581476
				else 9202
			end as VisitOccurrenceConceptId,    -- ""visit_concept_id""

			l1.RecordConnectionIdentifier,

			coalesce(l5.EpisodeStartDate, l5.StartDateHospitalProviderSpell, l1.CDSActivityDate) as VisitStartDate,  -- visit_start_date
			coalesce(l5.EpisodeStartTime, l5.StartTimeHospitalProviderSpell, '000000') as VisitStartTime,  -- visit_start_time

			coalesce(l5.EpisodeEndDate, l5.DischargeDateHospitalProviderSpell, l1.CDSActivityDate) as VisitEndDate,

			coalesce(l5.EpisodeEndTime, l5.DischargeTimeHospitalProviderSpell, '000000') as VisitEndTime,

			case 
				when l5.EpisodeEndDate is null and l5.DischargeDateHospitalProviderSpell is null and l5.PatientClassification = 1 then 32220
				else 32818
			end as VisitTypeConceptId,

			case 
				when l5.EpisodeEndDate is null and l5.DischargeDateHospitalProviderSpell is null and l5.PatientClassification = 1 then 2
				else 1
			end as RowPriority,

			l5.SourceofAdmissionCode,
			l5.DischargeDestinationCode
	from [omop_staging].[cds_line01] l1
		left join [omop_staging].[cds_line04] l4 
			on l1.MessageId = l4.MessageId -- Location Details
		left join [omop_staging].[cds_line05] l5 
			on l1.MessageId = l5.MessageId -- Hospital Provider Spell
		inner join dbo.Code c 
			on l1.ActivityTreatmentFunctionCode = c.Code
	where l1.CDSUpdateType = 9   -- New/Modification     (1 = Delete)
		and l1.NHSNumber is not null
		and c.CodeTypeId = 2 -- activity_treatment_function_code
		and not exists (select * from RecordsWithVariableNhsNumber rwvnn where rwvnn.RecordConnectionIdentifier = l1.RecordConnectionIdentifier)
), VisitDetailWithRank as (
	select
		*,
		row_number() over (partition by RecordConnectionIdentifier order by RowPriority asc) as RowRank
	from VisitDetail
)
select
	*
from VisitDetailWithRank
where RowRank = 1
		
	

Comment or raise an issue for this mapping.