discharged_to_concept_id

CDS VisitOccurrenceWithSpell

Source column DischargeDestinationCode. Lookup discharge destination concept.

DischargeDestinationCode	discharged_to_concept_id	notes
19	0	Home - Used 0 as `Home` as per the OHDSI documentation
29	8602	Temporary Lodging
30	38004284	Psychiatric Hospital
37	4050489	County court bailiff - Had to use the Social Context Domain and SNOMED Vocab
38	38003619	Prison / Correctional Facility - Went with `Prison / Correctional Facility` over Police Station (4107305)
48	38004284	Psychiatric Hospital
49	38004284	Psychiatric Hospital
50	8971	Inpatient Psychiatric Facility
51	8717	Inpatient Hospital
52	8650	Birthing Center
53	8976	Psychiatric Residential Treatment Center
54	8676	Nursing Facility
65	8676	Nursing Facility
66	38004205	Foster Care Agency
79		No mapping possible
84	8971	Inpatient Psychiatric Facility
85	8676	Nursing Facility
87	8717	Inpatient Hospital
88	8546	Hospice
98		No mapping possible
99		No mapping possible

Notes

Discharge Destination
DischargeDestinationCode Discharge Destination Code DISCHARGE DESTINATION CODE (HOSPITAL PROVIDER SPELL)

select
	l1.NHSNumber,
	l5.HospitalProviderSpellNumber,
	min (l5.EpisodeStartDate) as EpisodeStartDate,
	coalesce 
	(
		min (l5.EpisodeStartTime), 
		'000000'
	) as EpisodeStartTime,
	coalesce 
	(
		max (l5.EpisodeEndDate), 
		max (l1.CDSActivityDate)
	) as EpisodeEndDate,
	coalesce 
	(
		max (l5.EpisodeEndTime), 
		'000000'
	) as EpisodeEndTime,
	case 
		when max(l5.AdmissionMethodCode) in ('21','24') and max(l5.PatientClassification) = 1 then 262
        when max(l5.AdmissionMethodCode) in ('21','24') then 9203
        when max(l5.PatientClassification) in (1) then 9201
        when max(l4.LocationClass) in ('02') then 581476
		else 9202
	end as VisitOccurrenceConceptId,    -- "visit_concept_id"
	case 
		when max(l5.EpisodeEndDate) is null and max(l5.DischargeDateHospitalProviderSpell) is null then 32220
        else 32818
	end as VisitTypeConceptId,
	max (l5.SourceofAdmissionCode) as SourceofAdmissionCode,
	max (l5.DischargeDestinationCode) as 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 c.Code = l1.ActivityTreatmentFunctionCode
where l1.CDSUpdateType = 9   -- New/Modification     (1 = Delete)
	and l1.NHSNumber is not null
	and c.CodeTypeId = 2 -- activity_treatment_function_code
	and l5.HospitalProviderSpellNumber is not null
group by 
	l1.NHSNumber, 
	l5.HospitalProviderSpellNumber;
	

Comment or raise an issue for this mapping.

CDS VisitOccurrenceWithoutSpell

Source column DischargeDestinationCode. Lookup discharge destination concept.

DischargeDestinationCode	discharged_to_concept_id	notes
19	0	Home - Used 0 as `Home` as per the OHDSI documentation
29	8602	Temporary Lodging
30	38004284	Psychiatric Hospital
37	4050489	County court bailiff - Had to use the Social Context Domain and SNOMED Vocab
38	38003619	Prison / Correctional Facility - Went with `Prison / Correctional Facility` over Police Station (4107305)
48	38004284	Psychiatric Hospital
49	38004284	Psychiatric Hospital
50	8971	Inpatient Psychiatric Facility
51	8717	Inpatient Hospital
52	8650	Birthing Center
53	8976	Psychiatric Residential Treatment Center
54	8676	Nursing Facility
65	8676	Nursing Facility
66	38004205	Foster Care Agency
79		No mapping possible
84	8971	Inpatient Psychiatric Facility
85	8676	Nursing Facility
87	8717	Inpatient Hospital
88	8546	Hospice
98		No mapping possible
99		No mapping possible

Notes

Discharge Destination
DischargeDestinationCode Discharge Destination Code DISCHARGE DESTINATION CODE (HOSPITAL PROVIDER SPELL)

;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
)
select
	l1.NHSNumber,
	l1.RecordConnectionIdentifier,
	min (l1.CDSActivityDate) as EpisodeStartDate,
	'000000' as EpisodeStartTime,
	max (l1.CDSActivityDate) as EpisodeEndDate,
	'000000' as EpisodeEndTime,
	max (l5.SourceofAdmissionCode) as SourceofAdmissionCode,
	max (l5.DischargeDestinationCode) as 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 c.Code = l1.ActivityTreatmentFunctionCode
where l1.CDSUpdateType = 9   -- New/Modification     (1 = Delete)
	and l1.NHSNumber is not null
	and c.CodeTypeId = 2 -- activity_treatment_function_code
	and l5.HospitalProviderSpellNumber is null
	and not exists (select * from RecordsWithVariableNhsNumber rwvnn where rwvnn.RecordConnectionIdentifier = l1.RecordConnectionIdentifier)
group by 
	l1.NHSNumber, 
	l1.RecordConnectionIdentifier;
	

Comment or raise an issue for this mapping.