Patients

Patients

The following describes all the available to extractions Patients.
At least, to obtain table of Patients, it needs DCIR, IR_BEN_R and MCO.
At the beginning, it needs extractions Patients from different sources and then it builds a final table with all informations.

Patients in DCIR

This extraction is obtained from DCIR and algorithms as below determine informations taken into account for each patient.

Patient field	PatientID	gender	birthDate	deathDate
Mapping	NUM_ENQ from ER_PRS_F	BEN_SEX_COD from ER_PRS_F	BEN_NAI_ANN from ER_PRS_F	BEN_DCD_DTE from ER_PRS_F

gender algorithm

Filter gender != 9
The average rounded up of genders for each patient

birthDate algorithm

After selecting the data, the next step is to group by patientId and age, because we need to estimate the birthDate and we use min(eventDate) and max(eventDate) for each age to achieve that.

Then we aggregate again by taking the mean between the closest dates where the age changed. For example, if the patient was 60yo when an event happened on Apr/2010 and he was 61yo when another event happened on Jun/2010, we calculate the mean and estimate his birthday as being in May of the year found

deathDate algorithm

Filter gender != 9
Minimum of deathDate for each patient

Patients in IR_BEN_R

This extraction is obtained from IR_BEN_R and algorithms as below determine informations taken into account for each patient.

Patient field	PatientID	gender	birthDate	deathDate
Mapping	NUM_ENQ	BEN_SEX_COD	BEN_NAI_MOI-BEN_NAI_ANN	BEN_DCD_DTE

deathDate algorithm

Minimum of deathDate for each patient

Patients in MCO

This extraction is obtained from MCO and algorithms as below determine informations taken into account for each patient.

Patient field	PatientID	gender	birthDate	deathDate
Mapping	NUM_ENQ from MCO_B	null	null	SOR_MOI-SOR_ANN from MCO_B

deathDate algorithm

Minimum of deathDate for each patient when SOR_MOD = 9 (deathCode)

Patients in MCO_CE

This extraction is obtained from MCO_CE and algorithms as below determine informations taken into account for each patient.

Patient field	PatientID	gender	birthDate	deathDate
Mapping	NUM_ENQ from MCO_CSTC	COD_SEX from MCO_FASTC	EXE_SOI_DTD - AGE_ANN from MCO_FASTC	null

gender algorithm

Filter gender != 9
The average rounded up of genders for each patient

birthDate algorithm

After selecting the data, the next step is to group by patientId and age, because we need to estimate the birthDate and we use min(eventDate) and max(eventDate) for each age to achieve that.

Then we aggregate again by taking the mean between the closest dates where the age changed. For example, if the patient was 60yo when an event happened on Apr/2010 and he was 61yo when another event happened on Jun/2010, we calculate the mean and estimate his birthday as being in May of the year found

Patients in HAD

This extraction is obtained from HAD and algorithms as below determine informations taken into account for each patient.

Patient field	PatientID	gender	birthDate	deathDate
Mapping	NUM_ENQ from HAD_C	null	null	SOR_MOI-SOR_ANN from HAD_B

deathDate algorithm

Minimum of deathDate for each patient when SOR_MOD = 9 (deathCode)

Table final of Patients without MCO_CE

This table is obtained after joining Patients in IR_BEN_R with Patients in MCO with Patients in DCIR.
Filter birthDate != {null, -1, 0, 1, 1600}
Filter deathDate >= birthDate

Patient field	PatientID	gender	birthDate	deathDate
Mapping	DCIR -> IR_BEN_R -> MCO	IR_BEN_R -> DCIR	IR_BEN_R -> DCIR	IR_BEN_R -> DCIR -> MCO

Example :
If PatientID is not mentionned in DCIR, it takes it from IR_BEN_R and if it's not in IR_BEN_R, it takes it in MCO.
If birthDate from IR_BEN_R is null, it takes birthDate from DCIR

Table final of Patients with MCO_CE

This table is obtained after joining the table as noted above with Patients in MCO_CE.
Filter patientID != null, gender != null, birthDate != null

Patient field	PatientID	gender	birthDate	deathDate
Mapping	DCIR -> IR_BEN_R -> MCO -> MCO_CE	IR_BEN_R -> DCIR -> MCO_CE	IR_BEN_R -> DCIR -> MCO_CE	IR_BEN_R -> DCIR -> MCO -> MCO_CE