Domaine 'Condition' - DCIR

Les seuls diagnostics disponibles dans le DCIR sont les affections longue durée. (ALD).

Tables et variables du SNDS utilisées

• _IR_IMB_R
  • ben_nir_psa
  • imb_ald_dte
  • ins_date
  • imb_ald_dtf
  • imb_etm_nat
  • med_mtf_cod

Règles de transformation

Variables	Règles	Jointure		Filtres
		Gauche	Droite
id*	hash(source_value)
visit_occurrence_id	NULL
person_id*	person_id de PERSON Joindre _IR_IMB_R à PERSON sur ben_nir_psa = person_source_value
start_date*	imb_ald_dtd de _IR_IMB_R (si cette date (date de début de l’ALD) existe) Sinon ins_date de _IR_IMB_R (Date d’insertion dans la base)
start_datetime	start_date + ‘00:00:00’
end_date	imb_ald_dtf de _IR_IMB_R si la personne est encore en vie, sinon ben_dcd_dte Si imb_ald_dtf n’est pas renseignée (i.e, la date vaut ‘1600-01-01’ ou ‘2099-12-31’, on met la date de fin d’enregistrement pour la période.
end_datetime	end_date + ‘00:00:00’
type_concept_id*	32810
status_concept_id	0
status_source_value	imb_etm_nat de _IR_IMB_R
quantity	1
provider_id	NULL
source_value	med_mft_cod de _IR_IMB_R
source_concept_id	concept_id de CONCEPT	_IR_IMB_R med_mft_cod	CONCEPT concept_code	vocabulary_id = ‘CIM-10’
concept_id*	concept_id_2 de CONCEPT_RELATIONSHIP	Jointure 1 : _IR_IMB_R med_mft_cod Jointure 2: CONCEPT concept_id	Jointure 1: CONCEPT concept_code Jointure 2: CONCEPT_RELATIONSHIP concept_id_1	relationship = ‘Maps to’
domain_id	domain_id provenant de CONCEPT Si la jointure existe Sinon, mettre ‘Condition’	Jointure 1 : _IR_IMB_R med_mft_cod Jointure 2: CONCEPT concept_id Jointure 3: CONCEPT_RELATIONSHIP concept_id_2	Jointure 1: CONCEPT concept_code Jointure 2: CONCEPT_RELATIONSHIP concept_id_1 Jointure 3: CONCEPT concept_id	relationship = ‘Maps to’

Domaine 'Condition' - PMSI

Diagnostics principaux

Tables et variables du SNDS utilisées

T_HADaaB eta_num_epmsi rhad_num dgn_pal	T_MCOaaB eta_num rsa_num dgn_pal	T_MCOaaUM eta_num rsa_num dgn_pal	T_RIPaaRSA eta_num_epmsi rip_num dgn_pal sej_idt	T_SSRaaB eta_num rha_num rhs_num fp_pec (Finalité principale de prise en charge) mor_prp (Manifestation morbide prinsiaple) etl_aff (Affection étiologique)
T_HADaaDMPP eta_num_epmsi rhad_num dgn_ass_mpp			T_RIPaaC eta_num_epmsi rip_num exe_soi_dtd	T_SSRaaC eta_num rha_num exe_soi_dtd

Règles de transformation

Variable	Règles	Jointure		Filtre
		Gauche	Droite
id*	hash(source_value)
visit_occurrence_id	visit_occurrence_id de VISIT_OCCURRENCE	T_HADaaB ‘hadaa’ || ‘_’ || eta_num_epmsi || rhad_num T_HADaaDMPP ‘hadaa’ || eta_num_epmsi || rhad_num T_MCOaaB ‘mcoaa’ || ‘_’ || eta_num || ‘_’ || rsa_num T_MCOaaUM ‘mcoaa’ || ‘_’ || eta_num || ‘_’ || rsa_num T_RIPaaRSA ‘ripaa’ || ‘_’ || eta_num_epmsi || ‘_’ || sej_idt || ‘_’ || exe_soi_dtd T_SSRaaB ‘ssr’ || ‘_’ || eta_num || ‘_’ || rha_num || ‘_’ || exe_soi_dtd	VISIT_OCCURRENCE visit_occurrence_source_value
visit_detail_id	visit_detail_id de VISIT_DETAIL	T_SSRaaB ‘ssr_’ || eta_num || ‘_’ || rha_num || ‘_’ || rhs_num	VISIT_DETAIL visit_detail_source_value
person_id*	person_id de VISIT_OCCURRENCE	cf jointure visit_occurrence_id
start_date*	visit_start_date de VISIT_OCCURRENCE	cf jointure visit_occurrence_id
start_datetime	start_date + ‘00:00:00’
end_date	visit_end_date de VISIT_OCCURRENCE	cf jointure visit_occurrence_id
end_datetime	end_date + ‘00:00:00’
type_concept_id*	32810
status_concept_id	32902 (Primary diagnosis)
status_source_value	‘Diagnostic Principal’
quantity	1
provider_id	provider_id de VISIT_OCCURRENCE	cf jointure visit_occurrence_id
source_value	T_HADaaB : dgn_pal T_HADaaDMPP : dgn_ass_mpp T_MCOaaB : dgn_pal T_MCOaaUM : dgn_pal T_RIPaaRSA : dgn_pal T_SSRaaB : fp_pec, mor_prp etl_aff
source_concept_id	concept_id de CONCEPT	T_HADaaB : dgn_pal T_HADaaDMPP : dgn_ass_mpp T_MCOaaB : dgn_pal T_MCOaaUM : dgn_pal T_RIPaaRSA : dgn_pal T_SSRaaB : fp_pec T_SSRaaB : mor_prp T_SSRaaB : etl_aff	CONCEPT concept_code	vocabulary_id = ‘CIM-10’
concept_id*	concept_id_2 de CONCEPT_RELATIONSHIP	Jointure 1: T_HADaaB : dgn_pal T_HADaaDMPP : dgn_ass_mpp T_MCOaaB : dgn_pal T_MCOaaU : dgn_pal T_RIPaaRSA : dgn_pal T_SSRaaB : fp_pec T_SSRaaB : mor_prp T_SSRaaB : etl_aff	Jointure1: CONCEPT concept_code	vocabulary_id = ‘CIM-10’
		Jointure 2: CONCEPT concept_id	Jointure 2: CONCEPT_RELATIONSHIP concept_id_1	Jointure 2: relationship_id = ‘Maps to’
domain_id	domain_id de CONCEPT Si la jointure existe Sinon, mettre ‘Condition’	Jointure 1: T_HADaaB : dgn_pal T_HADaaDMPP : dgn_ass_mpp T_MCOaaB : dgn_pal T_MCOaaU : dgn_pal T_RIPaaRSA : dgn_pal T_SSRaaB : fp_pec T_SSRaaB : mor_prp T_SSRaaB : etl_aff	Jointure1: CONCEPT concept_code	vocabulary_id = ‘CIM-10’
		Jointure 2: CONCEPT concept_id	Jointure 2: CONCEPT_RELATIONSHIP concept_id_1	relationship_id = ‘Maps to’
		Jointure 3: CONCEPT_RELATIONSHIP concept_id_2	Jointure 3: CONCEPT concept_id

Diagnostics reliés et associés

Tables et variables du SNDS utilisées

T_MCOaaB eta_num rsa_num dgn_rel

T_MCOaaUM eta_num rsa_num deg_rel

T_MCOaaD eta_num rsa_num ass_dgn

T_HADaaD eta_num_epmsi rahd_num ass_dgn

T_HADaaDMPA eta_num_epmsi rhad_num dgn_ass_mpa

T_SSRaaD eta_um rha_num dgn_cod

T_SSRaaC eta_num rha_num exe_soi_dtd

Règles de transformation

Variable	Règle	Gauche	Droite	Filtre
id*	hash(source_value)
visit_occurrence_id	visit_occurrence_id de VISIT_OCCURRENCE	T_MCOaaB ‘mcoaa’ || ‘_’ || eta_num || ‘_’ || rsa_num T_MCOaaUM ‘mcoaa’ || ‘_’ || eta_num || ‘_’ || rsa_num T_MCOaaD ‘mcoaa’ || ‘_’ || eta_num || ‘_’ || rsa_num T_HADaaD ‘hadaa’ || ‘_’ || eta_num_epmsi || ‘_’ || rhad_num T_HADaaDMPA ‘hadaa’ || eta_num_epmsi || rhad_num T_SSRaaD ‘ssr’ || ‘_’ || eta_num || ‘_’ || rha_num || ‘_’ || exe_soi_dtd	VISIT_OCCURRENCE visit_occurrence_source_value
person_id*	person_id de VISIT_OCCURRENCE	cf explication visit_occurrence_id
start_date*	visit_start_date de VISIT_OCCURRENCE)	cf explication visit_occurrence_id
start_datetime	start_date + ‘00:00:00’
end_date	visit_end_date de VISIT_OCCURRENCE	cf explication visit_occurrence_id
end_datetime	end_date + ‘00:00:00’
type_concept_id*	32810
status_concept_id	32908 (Secondary diagnosis)
status_source_value	T_MCOaaB : ‘Diagnostic Relié’ T_MCOaaUM : ‘Diagnostic Relié’’ T_MCOaaD : ‘Diagnostic Associé’ T_HADaaD : ‘Diagnostic Associé’ T_HADaaDMPA : ‘Diagnostic lié au mode de prise en charge associé T_SSRaaD : ‘Diagnostic Associé’
quantity	1
provider_id	provider_id de VISIT_OCCURRENCE	cf explication visit_occurrence_id
source_value	T_MCOaaB : dgn_rel T_MCOaaUM : dgn_rel T_MCOaaD : ass_dgn T_HADaaD : dgn_ass T_SSRaaD : dgn_cod T_HADaaDMPA : dgn_ass_mpa
source_concept_id	concept_id de CONCEPT	T_MCOaaB : dgn_rel T_MCOaaUM : dgn_rel T_MCOaaD : ass_dgn T_HADaaD : dgn_ass T_HADaaDMPA : dgn_ass_mpa T_SSRaaD : dgn_cod	CONCEPT concept_code	vocabulary_id = ‘CIM-10’
concept_id*	concept_id_2 de CONCEPT_RELATIONSHIP	Jointure 1: T_MCOaaB : dgn_rel T_MCOaaUM : dgn_rel T_MCOaaD : ass_dgn T_HADaaD : dgn_ass T_HADaaDMPA : dgn_ass_mpa T_SSRaaD : dgn_cod	Jointure 1: CONCEPT concept_code	vocabulary_id = ‘CIM-10’
		Jointure 2: CONCEPT concept_id	Jointure 2: CONCEPT_ RELATIONSHIP concept_id_1	relationship_id = ‘Maps to’
domain_id	domain_id de CONCEPT s’il existe Sinon, mettre ‘Condition’	Jointure 1: T_MCOaaB : dgn_rel T_MCOaaUM : dgn_rel T_MCOaaD : ass_dgn T_HADaaD : dgn_ass T_HADaaDMPA : dgn_ass_mpa T_SSRaaD : dgn_cod	Jointure 1: CONCEPT concept_code	vocabulary_id = ‘CIM-10’
		Jointure 2: CONCEPT concept_id	Jointure 2: CONCEPT_ RELATIONSHIP concept_id_1	relationship_id = ‘Maps to’
		Jointure 3: CONCEPT_RELATIONSHIP concept_id_2	Jointure 3: CONCEPT concept_id