Data dictionary

Attempt at a easier to follow data dictionary. Is this what you had in mind? Yes, this is what I have in mind. Thanks. Some suggestions: I just notice some start dates are not the actual start dates - It is important for the users to know how far back the data are available so they can decide the covered period of their study. is it possible to change the date to actual earliest collection start date (not 1 Jan 1900). For the ICD10 and CCI, we can use the 1 Jan 2019 as the official start date. we can do similarly for the end dates of some discontinued table (similar in L_Labs). --JMojica 10:05, 2019 January 3 (CST) The start and end dates are stored on the individual pages. If the list shows 1900 then they were not filled in. If you know what they are and fill them in then this page will list them. Ttenbergen 14:49, 2019 January 3 (CST) add a column for program (Critical care only or Medicine only or both). I can do that, but the table is already getting quite wide for a web page. Can we lose any of the columns we currently have? The info you mean is always visible in the pages themselves as well, in case that's sufficient. But, yes, we can tweak what the tables should show. It is done in Template:DataDictionaryQuery and removing fields would be easy enough. I can show you how to add fields. Or I can add them once we confirm what we want.

• SMW

• Cargo

• Categories

I will do the following: add the TISS elements add some created variables (e.g. Age, LOS, Apache score and the other APACHE component points (GCS, Acute Physiological, Age, etc) , Charlson score, delays, etc). can you add a link here the article of the sites and units/wards we are collecting and also the corresponding start collection dates. This is usually being asked by users. How about info on some ongoing tmp projects - perhaps include a list of projects and the associated data being collected.

• SMW

• Cargo

• Categories

For an alphabetic listing, see Auto Data Dictionary.

Registry

In the collection tool CCMDB.mdb the registry data is all in the L_Log table. In the data processing tool CFE and the central repository Centralized data.mdb this data is split out from the other in L_Log for PHIA reasons.

L_PHI table

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw
LastName field	string	40	Last Name of patient	11 July 1988	1 January 2100	Raw
FirstName field	string	40	Last Name of patient	11 July 1988	1 January 2100	Raw
PHIN field	number	Long Integer	The patient's PHIN .	11 July 1988	1 January 2100	Raw
Date of Birth	date	not entered	Date of Birth (DOB) is the data a patient was born.	11 July 1988	1 January 2100	Raw
Chart number	string	8	The number used by medical records to uniquely identify a patient's chart; it is different for the same patients at different hospitals.	11 July 1988	1 January 2100	Raw
Notes field (L PHI)	string	not entered	Notes in L_PHI table is used by the data processor to comment on data ad-hoc where needed.	11 July 1988	1 January 2100	Raw

L_Log table - Registry

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
LastName field	string	40	Last Name of patient	11 July 1988	1 January 2100	Raw
FirstName field	string	40	Last Name of patient	11 July 1988	1 January 2100	Raw
PHIN field	number	Long Integer	The patient's PHIN .	11 July 1988	1 January 2100	Raw
Date of Birth	date	not entered	Date of Birth (DOB) is the data a patient was born.	11 July 1988	1 January 2100	Raw
Chart number	string	8	The number used by medical records to uniquely identify a patient's chart; it is different for the same patients at different hospitals.	11 July 1988	1 January 2100	Raw
DC Treatment	string	50	"DC" for critical care patients if life-support treatment is terminated, blank for all others.	11 July 1988	10 May 2022	Raw
Province field	string	2	Province in which the patient is registered with health care. If the patient is not eligible for health care, it records the province that they reside in.	11 July 1988	1 January 2100	Raw
Sex field	string	7	Biological sex of the patient at birth; options are "male" and "female".	11 July 1988	1 January 2100	Raw
Postal Code field	string	6	The patient's postal code.	1 January 2012	1 January 2100	Raw
Pre acute living situation field	number	long integer	Info about the living situation of the patient prior to the current hospitalization.	1 July 2016	1 January 2100	Raw
Previous Location field	number	long integer	The most recent previous physical location (with #exceptions) of a patient before arriving at the collection location.	1 July 2016	1 January 2100	Raw
Service/Location field	number	long integer	Legacy field replaced by Boarding Loc and Service tmp entry	1 July 2016	1 January 2100	Raw
Dispo field	number	long	The Dispo field contains information about what happens to the patient at the end of their admission.	1 July 2016	1 January 2100	Raw
Transfer Ready DtTm field	date	not entered	Date and time the intent to discharge a patient to a lower level in the Level of care hierarchy was documented.	1 June 1999	15 October 2020	Raw

ADT

Admission, discharge and transfer data

L_Log table - physiological components

Something went wrong with this query and it has no data - please tell Tina Tenbergen

APACHE

Apache has physiological components and diagnostic components.

L_Log table - physiological components

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Admit Type for APACHE II	string	10	The Admit Type for APACHE II is a way to classify patients' surgical status and one of the elements used to generate the APACHE score.	11 July 1988	1 January 2100	Raw
Chronic Health APACHE	string	30	Specific chronic pre-existing conditions used for APACHE score.		17 February 2022	Raw
Temperature	number	single	The patient's Temperature level in °C.	11 July 1988	1 January 2100	Raw
AP Sys BP Field	number	single	Ap_SysBP is the systolic blood pressure.	11 July 1988	1 January 2100	Raw
AP Dias BP Field	number	single	Ap_DiasBP is the diastolic blood pressure.	11 July 1988	1 January 2100	Raw
HR	number	single	The patient's Heart Rate level in beats/min.	11 July 1988	1 January 2100	Raw
RR	number	single	The patient's respiratory rate level in breaths/min.	11 July 1988	1 January 2100	Raw
AP Eye field	string	13	The eye component of the Glasgow_Coma_Scale.	11 July 1988	1 January 2100	Raw
AP Motor field	string	19	The motor component of the Glasgow_Coma_Scale.	11 July 1988	1 January 2100	Raw
AP Verbal field	string	26	The verbal component of the Glasgow_Coma_Scale.	11 July 1988	1 January 2100	Raw
FiO2	number	single	FIO2 is the fraction of inspired oxygen[1] in the gas mixture breathed by the patient.	11 July 1988	1 January 2100	Raw
GCS sedated field	boolean	not entered	Field does not contain any relevant data.	22 September 2016	15 June 2021	Raw
PO2	number	single	PO2 is the partial pressure of oxygen in the patient's arterial blood.	11 July 1988	1 January 2100	Raw
PCO2	number	single	PCO2 (or PaCO2) is the partial pressure of carbon dioxide (CO2) in the patient's arterial blood in mmol/L.	11 July 1988	1 January 2100	Raw
PH	number	single	The acidity or basicity of the patient's arterial blood.	11 July 1988	1 January 2100	Raw
Serum CO2	number	single	The patient's Serum CO2 level in mmol/L.	11 July 1988	1 January 2100	Raw
Na	number	single	The patient's Sodium level in mmol/L.	11 July 1988	1 January 2100	Raw
K	number	single	The patient's Potassium level in mmol/L.	11 July 1988	1 January 2100	Raw
HCT	number	single	The patient's hematocrit level in percentage.	11 July 1988	1 January 2100	Raw
WBC	number	single	The patient's White Blood Count level in x109/L.	11 July 1988	1 January 2100	Raw
Creatinine (APACHE)	number	single	The patient's level in .	11 July 1988	1 January 2100	Raw
ARF (APACHE)	number	single	The ARF checkbox is checked/true if patient is in Acute Renal Failure as per the APACHE definition.	11 July 1988	1 January 2100	Raw

Diagnostic components

• APACHE Comorbidities in ICD10 codes
• APACHE Acute Dxs in ICD10 codes

ADLs - Activities of Daily Living

Collected according to ADL General Collection Information.

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
ADL Bathing	string	15	Need for help with bathing; component of ADL.	1 October 2003	1 January 2100	Raw
ADL Dressing	string	15	Need for help with dressing; component of ADL.	1 October 2003	1 January 2100	Raw
ADL Toiletting	string	15	Need for help with toiletting; component of ADL	1 October 2003	1 January 2100	Raw
ADL Transfering	string	15	Need for help with transferring; component of ADL	1 October 2003	1 January 2100	Raw
ADL Continence	string	15	Control of urination and bowel movements; component of ADL	1 October 2003	1 January 2100	Raw
ADL Feeding	string	15	Need for help with feeding; component of ADL	1 October 2003	1 January 2100	Raw

Diagnoses

ICD10 - L_ICD10 table

This stores our version of ICD10 (see ICD10 Diagnosis List), collected following the ICD10 collection instructions.

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw
L ICD10 ID	number	long	Unique identifier of the L_ICD10 table	11 July 1988	1 January 2100	Raw
ICD10 code	string	10	The actual ICD10 code.	11 July 1988	1 January 2100	Raw
Dx Date	date	not entered	The date/time of an ICD10 diagnosis.	11 July 1988	1 January 2100	Raw
Dx Date unknown	boolean	not entered	True for Acquired Diagnosis / Complication when Dx Date legitimately missing.	11 July 1988	1 January 2100	Raw
Dx Type	string	8	The type of an ICD10 diagnosis (Comorbid Diagnosis, Admit Diagnosis or Acquired Diagnosis / Complication)	11 July 1988	1 January 2100	Raw
Dx Priority	number	single	The priority of an ICD10 diagnosis, used to rank and group.	11 July 1988	1 January 2100	Raw
Dx Primary	boolean	not entered	Checkbox that is set to true for a single Admit Diagnosis per ward stay that is the Primary Admit Diagnosis	11 July 1988	1 January 2100	Raw

Old dx schema

In this schema procedures were coded together with diagnoses.

Diagnoses - L_Dxs table

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw

Comorbids - L_Como table

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw

Procedures

This stores our version of CCI, collected following the CCI Collection instructions.

L_CCI_Picklist table

See CCI Picklist for possible values.

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw
Px Date	date	not entered	The date/time of a CCI procedure	11 July 1988	1 January 2100	Raw
CCI Picklist Code	string	20	The CCI code for and entry in the L CCI Picklist subform, from s_CCI_Picklist table.	1 January 2019	1 January 2100	Raw
Px Count	date	not entered	The type of a CCI procedure (comorbid, admit or acquired)	11 July 1988	1 January 2100	Raw
Px Type	string	not entered	The type of a CCI Procedure as per CCI Collection, ie admit or acquired	11 July 1988	1 January 2100	Raw

L_CCI_Component table

See CCI component 1 codes - what organ was something done to, CCI component 2 codes - what was done for possible values.

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw
Px Date	date	not entered	The date/time of a CCI procedure	11 July 1988	1 January 2100	Raw
Px Count	date	not entered	The type of a CCI procedure (comorbid, admit or acquired)	11 July 1988	1 January 2100	Raw
Px Type	string	not entered	The type of a CCI Procedure as per CCI Collection, ie admit or acquired	11 July 1988	1 January 2100	Raw

Old dx coding schema

In the old dx coding schema procedures were coded together with diagnoses, see #L_Dxs table.

Labs

CCI

See Converting Lab Counts to CCI for how the labs described in #Pre-CCI manual collection are now collected in CCI.

Pre-CCI manual collection

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw
CBC	integer	integer	CBC contains the number of CBCs done during a patient's stay in our unit.	11 July 1988	31 December 2018	Raw
PT PTT	integer	integer	PT_PTT contains the number of PTs and PTTs done during a patient's stay in our unit.	11 July 1988	17 March 2017	Raw
FFP	integer	integer	Fresh Frozen Plasma / FFP contains manually collected doses of FFP that were given.	11 July 1988	31 December 2018	Raw
Platelets	integer	integer	Platelets contains number of doses of platelets that were given.	11 July 1988	31 December 2018	Raw
Albumin 25pct	integer	integer	Albumin_25pct contains number of doses of 25% Albumin that were given.	11 July 1988	31 December 2018	Raw
Albumin 5pct	integer	integer	Albumin_5pct contains the number of doses of 5% Albumin that were given.	11 July 1988	31 December 2018	Raw
Creatinine (labs)	integer	integer	Creatinine contains the number of creatinines done during a patient's stay in our unit.	11 July 1988	17 March 2017	Raw
Magnesium (labs)	integer	integer	Magnesium contains the number of magnesiums done during a patient's stay in our unit.	11 July 1988	31 December 2018	Raw
SGPT ALT (labs)	integer	integer	SGPT_ALT contains the number of SGPTs and ALTs done during a patient's stay in our unit.	11 July 1988	17 March 2017	Raw
Troponin	integer	integer	Troponincontains the number of troponins done during a patient's stay in our unit.	11 July 1988	17 March 2017	Raw
CXR	integer	integer	Chest Xrays (CXR) contains the manually counted number of chest xrays (CXR) done during a patient's stay in our unit.	11 July 1988	31 December 2018	Raw
CT Scan (labs)	integer	integer	CT_Scan contains the manually counted number of CT scans done during a patient's stay in our unit.	11 July 1988	31 December 2018	Raw
Angio coronary field	integer	integer	Angio coronary contained the manually counted number of coronary angiograms done during a stay in our unit, or on the way there.	11 July 1988	3 April 2017	Raw
Angio others field	integer	integer	Angio_others contained the manually counted number of non-coronary angiograms done during a stay in our unit, or on the way there.	11 July 1988	3 April 2017	Raw
Echocardiogram	number	integer	Contains the number of heart ultrasounds (echocardiograms) a client has had during an admission to our unit.	11 July 1988	31 December 2018	Raw
Abdominal ultrasound	integer	integer	Contains the number of abdominal ultrasounds a client has had during an admission to our unit.	11 July 1988	31 December 2018	Raw
Sputum C+S	integer	integer	Contains the number of any sputum specimens (including endotracheal secretions) sent for "culture and sensitivity" for bacteria or fungus.	11 July 1988	17 March 2017	Raw
Blood culture	not entered	integer	Contains the number of blood cultures a patient has had during an admission to our unit.		17 March 2017	Raw
Urine CS	integer	integer	Urine_CS contains the number of any urine specimens sent for "culture and sensitivity" for bacteria or fungus.	11 July 1988	17 March 2017	Raw
Arterial blood gas (labs)	integer	long	ABG contains manually collected arterial blood gas counts.	11 July 1988	31 December 2018	Raw
Venous blood gas (labs)	integer	long	VBG contains manually collected venous blood gas counts.	11 July 1988	31 December 2018	Raw
Pentaspan	integer	integer	Pentaspan contains the number of doses or Pentaspan that were given while in the unit.		12 June 2014	Raw
MRI (labs)	integer	integer	MRI contains the manually counted number of MRIs done during a patient's stay in our unit.	11 July 1988	31 December 2018	Raw
Voluven	integer	integer	Voluven contains the number of doses or Voluven that were given while in the unit.	11 July 1988	12 June 2014	Raw

DSM Data

Something went wrong with this query and it has no data - please tell Tina Tenbergen

Pharmacy

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
PPI	number	integer	PPI contains manually collected doses of PPI that were given.	12 September 2011	12 May 2014	Raw
H2-Blockers	number	integer	H2-Blockers contains manually collected doses of H2-Blockers that were given.	24 December 2009	12 May 2014	Raw
Heparin SQ	number	integer	Heparin SQ contains manually collected doses of Heparin SQ that were given.	28 November 2011	13 May 2014	Raw
LMWH	number	integer	LMWH contains manually collected doses of LMWH that were given.	6 December 2011	31 December 2011	Raw
Benzodiazepines cont inf	number	integer	benzodiazepines cont inf contains manually collected doses of benzodiazepines cont inf that were given.	29 November 2011	13 May 2014	Raw
Opioids cont inf	number	integer	opioids cont inf contains manually collected doses of opioids cont inf that were given.	6 December 2011	13 May 2014	Raw
Propofol cont inf	number	integer	propofol cont inf contains manually collected doses of propofol cont inf that were given.	6 December 2011	13 May 2014	Raw
Insulin cont inf	number	integer	insulin cont inf contains manually collected doses of insulin cont inf that were given.	6 December 2011	13 May 2014	Raw
Furosemide cont inf	number	integer	furosemide cont inf contains manually collected doses of furosemide cont inf that were given.	21 December 2011	9 May 2014	Raw
Azoles	number	integer	azoles contains manually collected doses of azoles that were given.	11 July 1988	1 January 2100	Raw
Fungins	number	integer	fungins contains manually collected doses of fungins that were given.	11 July 1988	1 January 2100	Raw
Antipseudomonals	number	integer	antipseudomonals contains manually collected doses of antipseudomonals that were given.	11 July 1988	1 January 2100	Raw
Vancomycin	number	integer	vancomycin contains manually collected doses of vancomycin that were given.	11 July 1988	1 January 2100	Raw
Fluoroquinolones	number	integer	fluoroquinolones contains manually collected doses of fluoroquinolones that were given.	11 July 1988	1 January 2100	Raw
Carbapenems	number	integer	carbapenems contains manually collected doses of carbapenems that were given.	11 July 1988	1 January 2100	Raw
Vanco alternatives	number	integer	vanco alternatives contains manually collected doses of vanco alternatives that were given.	11 July 1988	1 January 2100	Raw
3rd generation cephalosporins	number	integer	3rd generation cephalosporins contains manually collected doses of 3rd generation cephalosporins that were given.	20 October 2011	11 March 2013	Raw
Ceftazidime	number	integer	ceftazidime contains manually collected doses of ceftazidime that were given.	2 January 2012	8 March 2013	Raw
Tigecyline	number	integer	tigecyline contains manually collected doses of tigecyline that were given.	21 July 2012	26 August 2012	Raw
Spare1	number	integer	spare1 contains manually collected doses of spare1 that were given.	8 December 2012	8 December 2012	Raw
Spare2	number	integer	spare2 contains manually collected doses of spare2 that were given.	8 December 2012	8 December 2012	Raw
Spare3	number	integer	spare3 contains manually collected doses of spare3 that were given.	8 December 2012	8 December 2012	Raw
Spare4	number	integer	spare4 contains manually collected doses of spare4 that were given.	8 December 2012	8 December 2012	Raw
Spare5	number	integer	spare5 contains manually collected doses of spare5 that were given.	8 December 2012	8 December 2012	Raw
Spare6	number	integer	spare6 contains manually collected doses of spare6 that were given.	8 December 2012	8 December 2012	Raw

Temporary project data

L_TmpV2 table

	FieldDataType	FieldLength	DataElementDescription	DataElementStartDate	DataElementEndDate	FieldCreatedRaw
Pat ID field	number	Long Integer	The Pat_ID field contains a unique-per-laptop identifying number for patient ward admissions. See Serial number.	11 July 1988	1 January 2100	Raw
D ID field	string	18	The unique identifier/index of records in the Critical Care and Medicine Database.	11 July 1988	1 January 2100	Raw