OpenSAFELY-TPP database schema
- Description
- This report provides information about the data schema for tables in the OpenSAFELY-TPP database.
- Contact
- Get in touch and tell us how you use this report or new features you'd like to see: team@opensafely.org
- First published
- 06 Jan 2021
- Last released
- 08 Jun 2022
- Links
Dataset schema in OpenSAFELY-TPP¶
This notebook displays the schema of the OpenSAFELY-TPP database. It is part of the technical documentation of the OpenSAFELY platform to help users understand the underlying data and guide analyses.
The schema information is read from the OpenSAFELYSchemaInformation
table, which is refreshed at the same time as the core S1
SystemOne tables. There are some non-automated steps required to update the schema information when a new table is added to the database — if you can't see a table that you are expecting to see, speak to TPP.
If you would like to apply to use the OpenSAFELY platform please read our documentation, the principles of the platform, and information about our pilot programme for onboarding external users.
If you want to see the Python code used to create this notebook, you can view it on GitHub.
Data sources¶
The core SystmOne primary care datasets are held in the S1
tables in the OpenSAFELY-TPP database. Other externally-linked data sources are listed below, with the table name given in brackets:
- All positive or negative SARS-CoV2 tests, from SGSS (
SGSS_AllTests_Positive
andSGSS_AllTests_Negative
) - First-ever positive or negative SARS-CoV2 test, from SGSS (
SGSS_Positive
andSGSS_Negative
) - A&E attendances, from SUS Emergency Care data (
EC
) - In-patient hospital admissions, from SUS Admitted Patient Care Spells data (
APCS
) - Out-patient hospital appointments, from SUS (
OPA
) - Covid-related ICU admissions, from ICNARC (
ICNARC
) - Covid-related in-hospital deaths, from CPNS (
CPNS
) - COVID-19 Infection Survey, from ONS (
ONS_CIS
) - All-cause registered deaths, from ONS (
ONS_Deaths
) - High cost drugs (
HighCostDrugs
) - Unique Property Reference Number, used for deriving household variables (
UPRN
) - Master Patient Index (
MPI
) - Health and Social Care Worker identification, collected at the point of vaccination (
HealthCareWorker
)
Some of these tables are accompanied by additional tables with further data. For instance, OPA
contains the core out-patient appointment event data, and is supplemented by the OPA_Cost
, OPA_Diag
, OPA_Proc
tables. See the data schema notebook for more information.
Notebook run date¶
This notebook was run on 2022-06-08. The information below reflects the state of the OpenSAFELY-TPP as at this date.
Table names by source¶
The table below lists all the data tables available in the OpenSAFELY-TPP database and where the data originate from.
DataSource | TableName | |
---|---|---|
0 | ICNARC | ICNARC |
1 | NHSD_HIGH_COST_DRUGS | HighCostDrugs |
2 | NHSE_APCS | APCS |
3 | NHSE_APCS | APCS_Cost |
4 | NHSE_APCS | APCS_Der |
5 | NHSE_CPNS | CPNS |
6 | NHSE_EC | EC |
7 | NHSE_EC | EC_AlcoholDrugInvolvement |
8 | NHSE_EC | EC_Comorbidities |
9 | NHSE_EC | EC_Cost |
10 | NHSE_EC | EC_Diagnosis |
11 | NHSE_EC | EC_Investigation |
12 | NHSE_EC | EC_PatientMentalHealth |
13 | NHSE_EC | EC_Treatment |
14 | NHSE_ECDS | ECDS |
15 | NHSE_ECDS | ECDS_EC_Diagnoses |
16 | NHSE_HEALTH_CARE_WORKER | HealthCareWorker |
17 | NHSE_MPI | MPI |
18 | NHSE_MPI | UPRN |
19 | NHSE_OPA | OPA |
20 | NHSE_OPA | OPA_Cost |
21 | NHSE_OPA | OPA_Diag |
22 | NHSE_OPA | OPA_Proc |
23 | NHSE_SGSS | SGSS_AllTests_Negative |
24 | NHSE_SGSS | SGSS_AllTests_Positive |
25 | NHSE_SGSS | SGSS_Negative |
26 | NHSE_SGSS | SGSS_Positive |
27 | NHSE_Therapeutics | Therapeutics |
28 | ONS_CIS | ONS_CIS |
29 | ONS_DEATHS | ONS_Deaths |
30 | OS_BUILD | BuildInfo |
31 | OS_BUILD | CodeCountIndicator |
32 | OS_BUILD | LatestBuildTime |
33 | OS_BUILD | OpenSAFELYSchemaInformation |
34 | OS_DERIVED | Household |
35 | OS_DERIVED | HouseholdMember |
36 | OS_DERIVED | MSOA_PopulationEstimates_2019 |
37 | OS_DERIVED | PotentialCareHomeAddress |
38 | S1 | Appointment |
39 | S1 | CTV3Dictionary |
40 | S1 | CTV3Hierarchy |
41 | S1 | CodedEvent |
42 | S1 | CodedEventRange |
43 | S1 | Consultation |
44 | S1 | DataDictionary |
45 | S1 | ICD10Dictionary |
46 | S1 | MedicationDictionary |
47 | S1 | MedicationIssue |
48 | S1 | MedicationRepeat |
49 | S1 | MedicationSensitivity |
50 | S1 | Organisation |
51 | S1 | Patient |
52 | S1 | PatientAddress |
53 | S1 | QOFClusterReference |
54 | S1 | RegistrationHistory |
55 | S1 | UnitDictionary |
56 | S1 | Vaccination |
57 | S1 | VaccinationReference |
58 | S1 | YCodeToSnomedMapping |
59 | UK_Renal_Register | UKRR |
Table Schema¶
The schema for each table contains the following info:
ColumnName
, the column name.ColumnType
, the column type, for example integer, numeric or date — see SQL Server data types documentation for more details.Precision
,Scale
andMaxLength
— see SQL Server precision, scale, and length documentation for more details.IsNullable
, are Null values accepted.
The schema for each table is printed below.
ICNARC¶
ICNARC¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
ICNARC_ID | bigint | 8 | 19 | 0 | True |
CalculatedAge | int | 4 | 10 | 0 | True |
EstimatedAge | int | 4 | 10 | 0 | True |
Sex | varchar | 10 | 0 | 0 | True |
OriginalHospitalAdmissionDate | datetime | 8 | 23 | 3 | True |
HospitalAdmissionDate | datetime | 8 | 23 | 3 | True |
IcuAdmissionDateTime | datetime | 8 | 23 | 3 | True |
TransferredIn | varchar | 10 | 0 | 0 | True |
OriginalIcuAdmissionDate | datetime | 8 | 23 | 3 | True |
HighestLevelFirst24Hours | int | 4 | 10 | 0 | True |
Ventilator | int | 4 | 10 | 0 | True |
AP2score | int | 4 | 10 | 0 | True |
IMscore | int | 4 | 10 | 0 | True |
pfratio | real | 4 | 24 | 0 | True |
BasicDays_RespiratorySupport | int | 4 | 10 | 0 | True |
AdvancedDays_RespiratorySupport | int | 4 | 10 | 0 | True |
BasicDays_CardiovascularSupport | int | 4 | 10 | 0 | True |
AdvancedDays_CardiovascularSupport | int | 4 | 10 | 0 | True |
SupportDays_Renal | int | 4 | 10 | 0 | True |
SupportDays_Neurological | int | 4 | 10 | 0 | True |
SupportDays_Gastrointestinal | int | 4 | 10 | 0 | True |
SupportDays_Dermatological | int | 4 | 10 | 0 | True |
SupportDays_Liver | int | 4 | 10 | 0 | True |
Level3days | int | 4 | 10 | 0 | True |
Level2days | int | 4 | 10 | 0 | True |
Level1days | int | 4 | 10 | 0 | True |
Level0days | int | 4 | 10 | 0 | True |
HRG | varchar | 50 | 0 | 0 | True |
yusurv | int | 4 | 10 | 0 | True |
IcuDischargeDateTime | datetime | 8 | 23 | 3 | True |
TransferredOut | varchar | 10 | 0 | 0 | True |
ausurv | int | 4 | 10 | 0 | True |
UltimateIcuDischargeDate | datetime | 8 | 23 | 3 | True |
yhsurv | int | 4 | 10 | 0 | True |
HospitalDischargeDate | datetime | 8 | 23 | 3 | True |
ahsurv | int | 4 | 10 | 0 | True |
UltimateHospitalDischargeDate | datetime | 8 | 23 | 3 | True |
DateOfDeath | datetime | 8 | 23 | 3 | True |
NHSD_HIGH_COST_DRUGS¶
HighCostDrugs¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
FinancialMonth | varchar | 2 | 0 | 0 | True |
FinancialYear | varchar | 6 | 0 | 0 | True |
PersonAge | int | 4 | 10 | 0 | True |
PersonGender | int | 4 | 10 | 0 | True |
ActivityTreatmentFunctionCode | varchar | 100 | 0 | 0 | True |
TherapeuticIndicationCode | varchar | 1000 | 0 | 0 | True |
HighCostTariffExcludedDrugCode | varchar | 100 | 0 | 0 | True |
DrugName | varchar | 1000 | 0 | 0 | True |
RouteOfAdministration | varchar | 100 | 0 | 0 | True |
DrugStrength | varchar | 1000 | 0 | 0 | True |
DrugVolume | varchar | 1000 | 0 | 0 | True |
DrugPackSize | varchar | 1000 | 0 | 0 | True |
DrugQuanitityOrWeightProportion | varchar | 1000 | 0 | 0 | True |
UnitOfMeasurement | varchar | 100 | 0 | 0 | True |
DispensingRoute | varchar | 100 | 0 | 0 | True |
HomeDeliveryCharge | varchar | 100 | 0 | 0 | True |
TotalCost | varchar | 100 | 0 | 0 | True |
DerivedSNOMEDFromName | varchar | 1000 | 0 | 0 | True |
DerivedVTM | varchar | 1000 | 0 | 0 | True |
DerivedVTMName | varchar | 1000 | 0 | 0 | True |
NHSE_APCS¶
APCS¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
APCS_Ident | bigint | 8 | 19 | 0 | False |
Carer_Support_Indicator | varchar | 1000 | 0 | 0 | True |
Ethnic_Group | varchar | 2 | 0 | 0 | True |
Administrative_Category | varchar | 2 | 0 | 0 | True |
Patient_Classification | varchar | 2 | 0 | 0 | True |
Admission_Method | varchar | 2 | 0 | 0 | True |
Discharge_Destination | varchar | 2 | 0 | 0 | True |
Discharge_Method | varchar | 2 | 0 | 0 | True |
Source_of_Admission | varchar | 2 | 0 | 0 | True |
Admission_Date | date | 3 | 10 | 0 | True |
Discharge_Date | date | 3 | 10 | 0 | True |
Provider_Org_Code_Type | varchar | 5 | 0 | 0 | True |
Duration_of_Elective_Wait | int | 4 | 10 | 0 | True |
Spell_Core_HRG_SUS | varchar | 10 | 0 | 0 | True |
Spell_HRG_Version_No_SUS | varchar | 20 | 0 | 0 | True |
Hospital_Spell_Duration | int | 4 | 10 | 0 | True |
Der_Spell_LoS | int | 4 | 10 | 0 | True |
Der_Diagnosis_Count | int | 4 | 10 | 0 | True |
Der_Procedure_Count | int | 4 | 10 | 0 | True |
Der_Diagnosis_All | varchar | 4000 | 0 | 0 | True |
Der_Procedure_All | varchar | 4000 | 0 | 0 | True |
Der_Admit_Treatment_Function_Code | varchar | 3 | 0 | 0 | True |
Der_Dischg_Treatment_Function_Code | varchar | 3 | 0 | 0 | True |
Der_Pseudo_Patient_Pathway_ID | bigint | 8 | 19 | 0 | True |
Der_Activity_Month | varchar | 6 | 0 | 0 | True |
Der_Financial_Year | varchar | 7 | 0 | 0 | True |
APCS_Cost¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
APCS_Ident | bigint | 8 | 19 | 0 | False |
Tariff_Initial_Amount | real | 4 | 24 | 0 | True |
Tariff_Total_Payment | real | 4 | 24 | 0 | True |
Grand_Total_Payment_MFF | real | 4 | 24 | 0 | True |
APCS_Der¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
APCS_Ident | bigint | 8 | 19 | 0 | False |
Spell_Dominant_Procedure | varchar | 100 | 0 | 0 | True |
Spell_Primary_Diagnosis | varchar | 5 | 0 | 0 | True |
Spell_Secondary_Diagnosis | varchar | 5 | 0 | 0 | True |
Spell_Treatment_Function_Code | varchar | 3 | 0 | 0 | True |
Spell_Main_Specialty_Code | varchar | 3 | 0 | 0 | True |
Spell_LoS | varchar | 5 | 0 | 0 | True |
Spell_PbR_CC_Day | varchar | 4 | 0 | 0 | True |
Spell_PbR_Rehab_Days | varchar | 4 | 0 | 0 | True |
Spell_RE30_Indicator | varchar | 1 | 0 | 0 | True |
Spell_RE30_Admit_Type | varchar | 1 | 0 | 0 | True |
NHSE_CPNS¶
CPNS¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Id | bigint | 8 | 19 | 0 | False |
LocationOfDeath | varchar | 1000 | 0 | 0 | True |
Sex | varchar | 5 | 0 | 0 | True |
DateOfAdmission | date | 3 | 10 | 0 | True |
DateOfSwabbed | date | 3 | 10 | 0 | True |
DateOfResult | date | 3 | 10 | 0 | True |
RelativesAware | varchar | 100 | 0 | 0 | True |
TravelHistory | varchar | 10 | 0 | 0 | True |
RegionCode | varchar | 10 | 0 | 0 | True |
RegionName | varchar | 100 | 0 | 0 | True |
OrganisationTypeLot | varchar | 100 | 0 | 0 | True |
RegionApproved | varchar | 10 | 0 | 0 | True |
RegionalApprovedDate | date | 3 | 10 | 0 | True |
NationalApproved | varchar | 10 | 0 | 0 | True |
NationalApprovedDate | date | 3 | 10 | 0 | True |
PreExistingCondition | varchar | 10 | 0 | 0 | True |
Age | int | 4 | 10 | 0 | True |
DateOfDeath | date | 3 | 10 | 0 | True |
HadLearningDisability | varchar | 10 | 0 | 0 | True |
ReceivedTreatmentForMentalHealth | varchar | 100 | 0 | 0 | True |
Der_Ethnic_Category_Description | varchar | 100 | 0 | 0 | True |
Der_Latest_SUS_Attendance_Date_For_Ethnicity | varchar | 100 | 0 | 0 | True |
Der_Source_Dataset_For_Ethnicty | varchar | 100 | 0 | 0 | True |
snapDate | date | 3 | 10 | 0 | True |
OnDeathCertificateNotice | bit | 1 | 1 | 0 | True |
CovidTestResult | varchar | 100 | 0 | 0 | True |
NHSworker | bit | 1 | 1 | 0 | True |
PreExistingConditionList | varchar | 4000 | 0 | 0 | True |
LearningDisabilityType | varchar | 100 | 0 | 0 | True |
TransferredFromLearningDisabilityAutismSetting | bit | 1 | 1 | 0 | True |
TransferredFromAMentalHealthSetting | bit | 1 | 1 | 0 | True |
DetainedUnderMHAct | bit | 1 | 1 | 0 | True |
NHSE_EC¶
EC¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
Ethnic_Category | varchar | 1 | 0 | 0 | True |
EC_Department_Type | varchar | 2 | 0 | 0 | True |
Arrival_Date | date | 3 | 10 | 0 | True |
Arrival_Time | time | 5 | 16 | 7 | True |
EC_Arrival_Mode_SNOMED_CT | varchar | 20 | 0 | 0 | True |
EC_AttendanceCategory | varchar | 1 | 0 | 0 | True |
EC_Attendance_Source_SNOMED_CT | varchar | 20 | 0 | 0 | True |
EC_Decision_To_Admit_Date | date | 3 | 10 | 0 | True |
Decision_To_Admit_Treatment_Function_Code | varchar | 3 | 0 | 0 | True |
Discharge_Destination_SNOMED_CT | varchar | 20 | 0 | 0 | True |
EC_Chief_Complaint_SNOMED_CT | varchar | 20 | 0 | 0 | True |
EC_Injury_Date | date | 3 | 10 | 0 | True |
SUS_HRG_Code | varchar | 5 | 0 | 0 | True |
SUS_Tariff | varchar | 5 | 0 | 0 | True |
SUS_Final_Price | varchar | 5 | 0 | 0 | True |
DQ_Chief_Complaint_Expected | varchar | 5 | 0 | 0 | True |
DQ_Chief_Complaint_Completed | varchar | 5 | 0 | 0 | True |
DQ_Chief_Complaint_Valid | varchar | 5 | 0 | 0 | True |
DQ_Primary_Diagnosis_Expected | varchar | 5 | 0 | 0 | True |
DQ_Primary_Diagnosis_Completed | varchar | 5 | 0 | 0 | True |
DQ_Primary_Diagnosis_Valid | varchar | 5 | 0 | 0 | True |
Der_EC_Diagnosis_All | varchar | 4000 | 0 | 0 | True |
Der_EC_Investigation_All | varchar | 4000 | 0 | 0 | True |
Der_EC_Treatment_All | varchar | 4000 | 0 | 0 | True |
Der_Activity_Month | varchar | 6 | 0 | 0 | True |
Der_Financial_Year | varchar | 7 | 0 | 0 | True |
EC_AlcoholDrugInvolvement¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
EC_Alcohol_Drug_Involvement_01 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_01 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_02 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_02 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_03 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_03 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_04 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_04 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_05 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_05 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_06 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_06 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_07 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_07 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_08 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_08 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_09 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_09 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_10 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_10 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_11 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_11 | varchar | 5 | 0 | 0 | True |
EC_Alcohol_Drug_Involvement_12 | varchar | 20 | 0 | 0 | True |
Is_Code_Approved_12 | varchar | 5 | 0 | 0 | True |
EC_Comorbidities¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
Comorbidity_01 | varchar | 20 | 0 | 0 | True |
Comorbidity_02 | varchar | 20 | 0 | 0 | True |
Comorbidity_03 | varchar | 20 | 0 | 0 | True |
Comorbidity_04 | varchar | 20 | 0 | 0 | True |
Comorbidity_05 | varchar | 20 | 0 | 0 | True |
Comorbidity_06 | varchar | 20 | 0 | 0 | True |
Comorbidity_07 | varchar | 20 | 0 | 0 | True |
Comorbidity_08 | varchar | 20 | 0 | 0 | True |
Comorbidity_09 | varchar | 20 | 0 | 0 | True |
Comorbidity_10 | varchar | 20 | 0 | 0 | True |
Comorbidity_11 | varchar | 20 | 0 | 0 | True |
Comorbidity_12 | varchar | 20 | 0 | 0 | True |
Comorbidity_13 | varchar | 20 | 0 | 0 | True |
Comorbidity_14 | varchar | 20 | 0 | 0 | True |
Comorbidity_15 | varchar | 20 | 0 | 0 | True |
Comorbidity_16 | varchar | 20 | 0 | 0 | True |
Comorbidity_17 | varchar | 20 | 0 | 0 | True |
Comorbidity_18 | varchar | 20 | 0 | 0 | True |
Comorbidity_19 | varchar | 20 | 0 | 0 | True |
Comorbidity_20 | varchar | 20 | 0 | 0 | True |
Comorbidity_21 | varchar | 20 | 0 | 0 | True |
Comorbidity_22 | varchar | 20 | 0 | 0 | True |
Comorbidity_23 | varchar | 20 | 0 | 0 | True |
Comorbidity_24 | varchar | 20 | 0 | 0 | True |
EC_Cost¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
Tariff_Total_Payment | real | 4 | 24 | 0 | True |
Grand_Total_Payment_MFF | real | 4 | 24 | 0 | True |
EC_Diagnosis¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
EC_Chief_Complaint_SNOMED_CT | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_01 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_02 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_03 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_04 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_05 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_06 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_07 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_08 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_09 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_10 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_11 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_12 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_13 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_14 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_15 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_16 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_17 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_18 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_19 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_20 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_21 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_22 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_23 | varchar | 20 | 0 | 0 | True |
EC_Diagnosis_24 | varchar | 20 | 0 | 0 | True |
AEA_Diagnosis_01 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_02 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_03 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_04 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_05 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_06 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_07 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_08 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_09 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_10 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_11 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_12 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_13 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_14 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_15 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_16 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_17 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_18 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_19 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_20 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_21 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_22 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_23 | varchar | 100 | 0 | 0 | True |
AEA_Diagnosis_24 | varchar | 100 | 0 | 0 | True |
EC_Investigation¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
EC_Investigation_01 | varchar | 20 | 0 | 0 | True |
EC_Investigation_02 | varchar | 20 | 0 | 0 | True |
EC_Investigation_03 | varchar | 20 | 0 | 0 | True |
EC_Investigation_04 | varchar | 20 | 0 | 0 | True |
EC_Investigation_05 | varchar | 20 | 0 | 0 | True |
EC_Investigation_06 | varchar | 20 | 0 | 0 | True |
EC_Investigation_07 | varchar | 20 | 0 | 0 | True |
EC_Investigation_08 | varchar | 20 | 0 | 0 | True |
EC_Investigation_09 | varchar | 20 | 0 | 0 | True |
EC_Investigation_10 | varchar | 20 | 0 | 0 | True |
EC_Investigation_11 | varchar | 20 | 0 | 0 | True |
EC_Investigation_12 | varchar | 20 | 0 | 0 | True |
EC_Investigation_13 | varchar | 20 | 0 | 0 | True |
EC_Investigation_14 | varchar | 20 | 0 | 0 | True |
EC_Investigation_15 | varchar | 20 | 0 | 0 | True |
EC_Investigation_16 | varchar | 20 | 0 | 0 | True |
EC_Investigation_17 | varchar | 20 | 0 | 0 | True |
EC_Investigation_18 | varchar | 20 | 0 | 0 | True |
EC_Investigation_19 | varchar | 20 | 0 | 0 | True |
EC_Investigation_20 | varchar | 20 | 0 | 0 | True |
EC_Investigation_21 | varchar | 20 | 0 | 0 | True |
EC_Investigation_22 | varchar | 20 | 0 | 0 | True |
EC_Investigation_23 | varchar | 20 | 0 | 0 | True |
EC_Investigation_24 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_01 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_02 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_03 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_04 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_05 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_06 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_07 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_08 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_09 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_10 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_11 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_12 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_13 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_14 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_15 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_16 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_17 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_18 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_19 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_20 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_21 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_22 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_23 | varchar | 20 | 0 | 0 | True |
AEA_Investigation_24 | varchar | 20 | 0 | 0 | True |
EC_PatientMentalHealth¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
MH_Classification_01 | varchar | 20 | 0 | 0 | True |
MH_Start_Date_01 | date | 3 | 10 | 0 | True |
MH_Expiry_Date_01 | date | 3 | 10 | 0 | True |
... | ... | ... | ... | ... | ... |
MH_Start_Date_23 | date | 3 | 10 | 0 | True |
MH_Expiry_Date_23 | date | 3 | 10 | 0 | True |
MH_Classification_24 | varchar | 20 | 0 | 0 | True |
MH_Start_Date_24 | date | 3 | 10 | 0 | True |
MH_Expiry_Date_24 | date | 3 | 10 | 0 | True |
74 rows × 5 columns
EC_Treatment¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | False |
EC_Treatment_01 | varchar | 20 | 0 | 0 | True |
EC_Treatment_02 | varchar | 20 | 0 | 0 | True |
EC_Treatment_03 | varchar | 20 | 0 | 0 | True |
EC_Treatment_04 | varchar | 20 | 0 | 0 | True |
EC_Treatment_05 | varchar | 20 | 0 | 0 | True |
EC_Treatment_06 | varchar | 20 | 0 | 0 | True |
EC_Treatment_07 | varchar | 20 | 0 | 0 | True |
EC_Treatment_08 | varchar | 20 | 0 | 0 | True |
EC_Treatment_09 | varchar | 20 | 0 | 0 | True |
EC_Treatment_10 | varchar | 20 | 0 | 0 | True |
EC_Treatment_11 | varchar | 20 | 0 | 0 | True |
EC_Treatment_12 | varchar | 20 | 0 | 0 | True |
EC_Treatment_13 | varchar | 20 | 0 | 0 | True |
EC_Treatment_14 | varchar | 20 | 0 | 0 | True |
EC_Treatment_15 | varchar | 20 | 0 | 0 | True |
EC_Treatment_16 | varchar | 20 | 0 | 0 | True |
EC_Treatment_17 | varchar | 20 | 0 | 0 | True |
EC_Treatment_18 | varchar | 20 | 0 | 0 | True |
EC_Treatment_19 | varchar | 20 | 0 | 0 | True |
EC_Treatment_20 | varchar | 20 | 0 | 0 | True |
EC_Treatment_21 | varchar | 20 | 0 | 0 | True |
EC_Treatment_22 | varchar | 20 | 0 | 0 | True |
EC_Treatment_23 | varchar | 20 | 0 | 0 | True |
EC_Treatment_24 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_01 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_02 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_03 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_04 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_05 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_06 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_07 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_08 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_09 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_10 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_11 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_12 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_13 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_14 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_15 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_16 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_17 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_18 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_19 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_20 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_21 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_22 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_23 | varchar | 20 | 0 | 0 | True |
AEA_Treatment_24 | varchar | 20 | 0 | 0 | True |
NHSE_ECDS¶
ECDS¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | True |
EC_PCD_Indicator | int | 4 | 10 | 0 | True |
CDS_Type | varchar | 3 | 0 | 0 | True |
CDS_Group_Indicator | bigint | 8 | 19 | 0 | True |
... | ... | ... | ... | ... | ... |
Der_EC_Departure_Date_Time | datetime | 8 | 23 | 3 | True |
Der_EC_Duration | int | 4 | 10 | 0 | True |
Der_Dupe_Flag | int | 4 | 10 | 0 | True |
Der_Record_Type | varchar | 4 | 0 | 0 | True |
Der_AEA_Patient_Group | varchar | 2 | 0 | 0 | True |
143 rows × 5 columns
ECDS_EC_Diagnoses¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
EC_Ident | bigint | 8 | 19 | 0 | True |
Ordinal | int | 4 | 10 | 0 | False |
DiagnosisCode | varchar | 50 | 0 | 0 | True |
NHSE_HEALTH_CARE_WORKER¶
HealthCareWorker¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
HealthCareWorker | varchar | 10 | 0 | 0 | False |
NHSE_MPI¶
MPI¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Gender | varchar | 1 | 0 | 0 | True |
Birth_Month | varchar | 10 | 0 | 0 | True |
Death_Month | varchar | 10 | 0 | 0 | True |
RP_of_Death | varchar | 10 | 0 | 0 | True |
DateFrom | date | 3 | 10 | 0 | True |
DateTo | date | 3 | 10 | 0 | True |
Date_Added | date | 3 | 10 | 0 | True |
Original_Posting_Date | date | 3 | 10 | 0 | True |
Data_Source | varchar | 100 | 0 | 0 | True |
Latest_Flag | varchar | 10 | 0 | 0 | True |
Care_Home_Flag | varchar | 10 | 0 | 0 | True |
ServiceType | varchar | 100 | 0 | 0 | True |
Living_Alone_Flag | varchar | 100 | 0 | 0 | True |
Living_with_young_Flag | varchar | 100 | 0 | 0 | True |
Living_with_elderly_Flag | varchar | 100 | 0 | 0 | True |
OS_Property_Classification | varchar | 100 | 0 | 0 | True |
Rural_Urban_Classification | varchar | 100 | 0 | 0 | True |
property_type | varchar | 100 | 0 | 0 | True |
private_outdoor_space | varchar | 100 | 0 | 0 | True |
private_outdoor_space_area | varchar | 100 | 0 | 0 | True |
Pseudo_uprn | varchar | 200 | 0 | 0 | True |
Pseudo_parent_uprn | varchar | 200 | 0 | 0 | True |
UPRN¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Pseudo_uprn | varchar | 200 | 0 | 0 | True |
Pseudo_parent_uprn | varchar | 200 | 0 | 0 | True |
class | varchar | 100 | 0 | 0 | True |
Total_Pop | int | 4 | 10 | 0 | True |
_0to4 | int | 4 | 10 | 0 | True |
_5to9 | int | 4 | 10 | 0 | True |
_10to14 | int | 4 | 10 | 0 | True |
_15to19 | int | 4 | 10 | 0 | True |
_20to24 | int | 4 | 10 | 0 | True |
_25to29 | int | 4 | 10 | 0 | True |
_30to34 | int | 4 | 10 | 0 | True |
_40to44 | int | 4 | 10 | 0 | True |
_45to49 | int | 4 | 10 | 0 | True |
_50to54 | int | 4 | 10 | 0 | True |
_55to59 | int | 4 | 10 | 0 | True |
_60to64 | int | 4 | 10 | 0 | True |
_65to69 | int | 4 | 10 | 0 | True |
_70to74 | int | 4 | 10 | 0 | True |
_75to79 | int | 4 | 10 | 0 | True |
_80to84 | int | 4 | 10 | 0 | True |
_85Plus | int | 4 | 10 | 0 | True |
Care_Home_Flag | varchar | 100 | 0 | 0 | True |
ServiceType | varchar | 100 | 0 | 0 | True |
Rural_Urban_Classification | varchar | 100 | 0 | 0 | True |
property_type | varchar | 100 | 0 | 0 | True |
private_outdoor_space | varchar | 100 | 0 | 0 | True |
private_outdoor_space_area | varchar | 100 | 0 | 0 | True |
NHSE_OPA¶
OPA¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
OPA_Ident | bigint | 8 | 19 | 0 | False |
Ethnic_Category | varchar | 2 | 0 | 0 | True |
Main_Specialty_Code | varchar | 3 | 0 | 0 | True |
Treatment_Function_Code | varchar | 3 | 0 | 0 | True |
MultiProf_Ind_Code | varchar | 2 | 0 | 0 | True |
Administrative_Category | varchar | 2 | 0 | 0 | True |
Attendance_Status | varchar | 2 | 0 | 0 | True |
First_Attendance | varchar | 2 | 0 | 0 | True |
Medical_Staff_Type_Seeing_Patient | varchar | 2 | 0 | 0 | True |
Operation_Status | varchar | 2 | 0 | 0 | True |
Outcome_of_Attendance | varchar | 2 | 0 | 0 | True |
Appointment_Date | date | 3 | 10 | 0 | True |
Consultation_Medium_Used | varchar | 2 | 0 | 0 | True |
Activity_Location_Type_Code | varchar | 5 | 0 | 0 | True |
Clinic_Code | varchar | 100 | 0 | 0 | True |
Provider_Code | varchar | 100 | 0 | 0 | True |
Provider_Code_Type | varchar | 100 | 0 | 0 | True |
Priority_Type | varchar | 2 | 0 | 0 | True |
OPA_Referral_Source | varchar | 2 | 0 | 0 | True |
Referral_Request_Received_Date | date | 3 | 10 | 0 | True |
HRG_Code | varchar | 10 | 0 | 0 | True |
HRG_Version_No | varchar | 10 | 0 | 0 | True |
Der_Activity_Month | varchar | 100 | 0 | 0 | True |
Der_Financial_Year | varchar | 100 | 0 | 0 | True |
OPA_Cost¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
OPA_Ident | bigint | 8 | 19 | 0 | False |
Tariff_OPP | real | 4 | 24 | 0 | True |
Tariff_Total_Payment | real | 4 | 24 | 0 | True |
Grand_Total_Payment_MFF | real | 4 | 24 | 0 | True |
OPA_Diag¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
OPA_Ident | bigint | 8 | 19 | 0 | False |
Primary_Diagnosis_Code | varchar | 100 | 0 | 0 | True |
Secondary_Diagnosis_Code_1 | varchar | 100 | 0 | 0 | True |
Primary_Diagnosis_Code_Read | varchar | 5 | 0 | 0 | True |
Secondary_Diagnosis_Code_1_Read | varchar | 5 | 0 | 0 | True |
OPA_Proc¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
OPA_Ident | bigint | 8 | 19 | 0 | False |
Primary_Procedure_Code | varchar | 100 | 0 | 0 | True |
Procedure_Code_2 | varchar | 100 | 0 | 0 | True |
Primary_Procedure_Code_Read | varchar | 5 | 0 | 0 | True |
Procedure_Code_2_Read | varchar | 5 | 0 | 0 | True |
NHSE_SGSS¶
SGSS_AllTests_Negative¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Organism_Species_Name | varchar | 200 | 0 | 0 | True |
Specimen_Date | date | 3 | 10 | 0 | True |
Lab_Report_Date | date | 3 | 10 | 0 | True |
Age_In_Years | int | 4 | 10 | 0 | True |
Patient_Sex | varchar | 50 | 0 | 0 | True |
County_Description | varchar | 50 | 0 | 0 | True |
PostCode_Source | varchar | 50 | 0 | 0 | True |
Symptomatic | varchar | 50 | 0 | 0 | True |
Ethnic_Category_Desc | varchar | 255 | 0 | 0 | True |
Pillar | varchar | 255 | 0 | 0 | True |
LFT_Flag | varchar | 255 | 0 | 0 | True |
SGSS_AllTests_Positive¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Organism_Species_Name | varchar | 200 | 0 | 0 | True |
Specimen_Date | date | 3 | 10 | 0 | True |
Lab_Report_Date | date | 3 | 10 | 0 | True |
Age_In_Years | int | 4 | 10 | 0 | True |
Patient_Sex | varchar | 50 | 0 | 0 | True |
County_Description | varchar | 50 | 0 | 0 | True |
PostCode_Source | varchar | 50 | 0 | 0 | True |
Symptomatic | varchar | 50 | 0 | 0 | True |
Ethnic_Category_Desc | varchar | 255 | 0 | 0 | True |
Pillar | varchar | 255 | 0 | 0 | True |
LFT_Flag | varchar | 255 | 0 | 0 | True |
Variant | varchar | 255 | 0 | 0 | True |
VariantDetectionMethod | varchar | 255 | 0 | 0 | True |
SGTF | varchar | 255 | 0 | 0 | True |
SGSS_Negative¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Organism_Species_Name | varchar | 200 | 0 | 0 | True |
Earliest_Specimen_Date | date | 3 | 10 | 0 | True |
Lab_Report_Date | date | 3 | 10 | 0 | True |
Age_In_Years | int | 4 | 10 | 0 | True |
Patient_Sex | varchar | 50 | 0 | 0 | True |
County_Description | varchar | 50 | 0 | 0 | True |
PostCode_Source | varchar | 50 | 0 | 0 | True |
SGSS_Positive¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Organism_Species_Name | varchar | 200 | 0 | 0 | True |
Earliest_Specimen_Date | date | 3 | 10 | 0 | True |
Lab_Report_Date | date | 3 | 10 | 0 | True |
Age_In_Years | int | 4 | 10 | 0 | True |
Patient_Sex | varchar | 50 | 0 | 0 | True |
County_Description | varchar | 50 | 0 | 0 | True |
PostCode_Source | varchar | 50 | 0 | 0 | True |
SGTF | varchar | 10 | 0 | 0 | False |
CaseCategory | varchar | 50 | 0 | 0 | False |
NHSE_Therapeutics¶
Therapeutics¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
AgeAtReceivedDate | int | 4 | 10 | 0 | True |
Received | datetime | 8 | 23 | 3 | True |
Intervention | varchar | 1000 | 0 | 0 | True |
Diagnosis | varchar | 1000 | 0 | 0 | True |
CurrentStatus | varchar | 1000 | 0 | 0 | True |
FormName | varchar | 1000 | 0 | 0 | True |
TreatmentStartDate | datetime | 8 | 23 | 3 | True |
Region | varchar | 1000 | 0 | 0 | True |
MOL1_onset_of_symptoms | varchar | 1000 | 0 | 0 | True |
MOL1_high_risk_cohort | varchar | 1000 | 0 | 0 | True |
SOT02_onset_of_symptoms | varchar | 1000 | 0 | 0 | True |
SOT02_risk_cohorts | varchar | 1000 | 0 | 0 | True |
CASIM05_date_of_symptom_onset | varchar | 1000 | 0 | 0 | True |
CASIM05_risk_cohort | varchar | 1000 | 0 | 0 | True |
COVID_indication | varchar | 1000 | 0 | 0 | True |
Count | int | 4 | 10 | 0 | True |
Der_LoadDate | varchar | 1000 | 0 | 0 | True |
ONS_CIS¶
ONS_CIS¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
visit_id | varchar | 100 | 0 | 0 | True |
dataset | int | 4 | 10 | 0 | True |
visit_date | date | 3 | 10 | 0 | True |
samples_taken_date | date | 3 | 10 | 0 | True |
... | ... | ... | ... | ... | ... |
patient_facing_clean | int | 4 | 10 | 0 | True |
work_status_clean | int | 4 | 10 | 0 | True |
SOC_occupation | varchar | 100 | 0 | 0 | True |
geography_name | varchar | 100 | 0 | 0 | True |
geography_code | varchar | 100 | 0 | 0 | True |
93 rows × 5 columns
ONS_DEATHS¶
ONS_Deaths¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
sex | varchar | 10 | 0 | 0 | True |
ageinyrs | int | 4 | 10 | 0 | True |
dod | date | 3 | 10 | 0 | True |
FIC10UND | varchar | 100 | 0 | 0 | True |
icd10u | varchar | 100 | 0 | 0 | True |
ICD10001 | varchar | 100 | 0 | 0 | True |
ICD10002 | varchar | 100 | 0 | 0 | True |
ICD10003 | varchar | 100 | 0 | 0 | True |
ICD10004 | varchar | 100 | 0 | 0 | True |
ICD10005 | varchar | 100 | 0 | 0 | True |
ICD10006 | varchar | 100 | 0 | 0 | True |
ICD10007 | varchar | 100 | 0 | 0 | True |
ICD10008 | varchar | 100 | 0 | 0 | True |
ICD10009 | varchar | 100 | 0 | 0 | True |
ICD10010 | varchar | 100 | 0 | 0 | True |
ICD10011 | varchar | 100 | 0 | 0 | True |
ICD10012 | varchar | 100 | 0 | 0 | True |
ICD10013 | varchar | 100 | 0 | 0 | True |
ICD10014 | varchar | 100 | 0 | 0 | True |
ICD10015 | varchar | 100 | 0 | 0 | True |
FIC10MEN1 | varchar | 100 | 0 | 0 | True |
FIC10MEN2 | varchar | 100 | 0 | 0 | True |
FIC10MEN3 | varchar | 100 | 0 | 0 | True |
FIC10MEN4 | varchar | 100 | 0 | 0 | True |
FIC10MEN5 | varchar | 100 | 0 | 0 | True |
FIC10MEN6 | varchar | 100 | 0 | 0 | True |
FIC10MEN7 | varchar | 100 | 0 | 0 | True |
FIC10MEN8 | varchar | 100 | 0 | 0 | True |
FIC10MEN9 | varchar | 100 | 0 | 0 | True |
FIC10MEN10 | varchar | 100 | 0 | 0 | True |
FIC10MEN11 | varchar | 100 | 0 | 0 | True |
FIC10MEN12 | varchar | 100 | 0 | 0 | True |
FIC10MEN13 | varchar | 100 | 0 | 0 | True |
FIC10MEN14 | varchar | 100 | 0 | 0 | True |
FIC10MEN15 | varchar | 100 | 0 | 0 | True |
Place_of_occurrence | varchar | 1000 | 0 | 0 | True |
OS_BUILD¶
BuildInfo¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
BuildDesc | varchar | 100 | 0 | 0 | False |
BuildDate | datetime | 8 | 23 | 3 | False |
BuildNumber | int | 4 | 10 | 0 | False |
CodeCountIndicator¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
CTV3Code | varchar | 50 | 0 | 0 | False |
CodeCountIndicator | float | 8 | 53 | 0 | True |
LatestBuildTime¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
DtLatestBuild | datetime | 8 | 23 | 3 | False |
OpenSAFELYSchemaInformation¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
TableName | nvarchar | 256 | 0 | 0 | False |
TableName | sysname | 256 | 0 | 0 | False |
ColumnId | int | 4 | 10 | 0 | False |
ColumnName | nvarchar | 256 | 0 | 0 | True |
ColumnName | sysname | 256 | 0 | 0 | True |
ColumnType | nvarchar | 256 | 0 | 0 | False |
ColumnType | sysname | 256 | 0 | 0 | False |
MaxLength | smallint | 2 | 5 | 0 | False |
Precision | tinyint | 1 | 3 | 0 | False |
Scale | tinyint | 1 | 3 | 0 | False |
CollationName | nvarchar | 256 | 0 | 0 | True |
CollationName | sysname | 256 | 0 | 0 | True |
IsNullable | bit | 1 | 1 | 0 | True |
DataSource | varchar | 1000 | 0 | 0 | True |
OS_DERIVED¶
Household¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Household_ID | bigint | 8 | 19 | 0 | True |
MSOA | varchar | 50 | 0 | 0 | True |
NFA_Unknown | bit | 1 | 1 | 0 | True |
CareHome | bit | 1 | 1 | 0 | True |
Prison | bit | 1 | 1 | 0 | True |
HouseholdSize | int | 4 | 10 | 0 | True |
MatchesUprnCount | bit | 1 | 1 | 0 | True |
MixedSoftwareHousehold | bit | 1 | 1 | 0 | True |
TppPercentage | int | 4 | 10 | 0 | True |
HouseholdMember¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
HouseholdMember_ID | bigint | 8 | 19 | 0 | False |
Patient_ID | bigint | 8 | 19 | 0 | False |
Household_ID | bigint | 8 | 19 | 0 | False |
MSOA_PopulationEstimates_2019¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
MSOA_Code | varchar | 50 | 0 | 0 | False |
LA_Code_2019 | varchar | 50 | 0 | 0 | False |
LA_Code_2020 | varchar | 50 | 0 | 0 | False |
Age_All | int | 4 | 10 | 0 | False |
Age_0 | int | 4 | 10 | 0 | False |
... | ... | ... | ... | ... | ... |
Age_86 | int | 4 | 10 | 0 | False |
Age_87 | int | 4 | 10 | 0 | False |
Age_88 | int | 4 | 10 | 0 | False |
Age_89 | int | 4 | 10 | 0 | False |
Age_90_Plus | int | 4 | 10 | 0 | False |
95 rows × 5 columns
PotentialCareHomeAddress¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
PatientAddress_ID | bigint | 8 | 19 | 0 | False |
LocationRequiresNursing | varchar | 5 | 0 | 0 | False |
LocationDoesNotRequireNursing | varchar | 5 | 0 | 0 | False |
S1¶
Appointment¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Appointment_ID | bigint | 8 | 19 | 0 | False |
Organisation_ID | bigint | 8 | 19 | 0 | False |
BookedDate | datetime | 8 | 23 | 3 | False |
StartDate | datetime | 8 | 23 | 3 | False |
EndDate | datetime | 8 | 23 | 3 | False |
ArrivedDate | datetime | 8 | 23 | 3 | False |
SeenDate | datetime | 8 | 23 | 3 | False |
FinishedDate | datetime | 8 | 23 | 3 | False |
Status | int | 4 | 10 | 0 | False |
CTV3Dictionary¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
CTV3Code | varchar | 50 | 0 | 0 | False |
Description | varchar | 255 | 0 | 0 | False |
CTV3Hierarchy¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
ParentCTV3Code | varchar | 50 | 0 | 0 | False |
ParentCTV3Description | varchar | 255 | 0 | 0 | False |
ChildCTV3Code | varchar | 50 | 0 | 0 | False |
ChildCTV3Description | varchar | 255 | 0 | 0 | False |
ChildToParentDistance | int | 4 | 10 | 0 | False |
CodedEvent¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Consultation_ID | bigint | 8 | 19 | 0 | False |
CodedEvent_ID | bigint | 8 | 19 | 0 | False |
CTV3Code | varchar | 50 | 0 | 0 | False |
NumericValue | real | 4 | 24 | 0 | False |
ConsultationDate | datetime | 8 | 23 | 3 | False |
CodedEventRange¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | int | 4 | 10 | 0 | False |
CodedEvent_ID | bigint | 8 | 19 | 0 | False |
CodedEventRange_ID | bigint | 8 | 19 | 0 | False |
Consultation_ID | bigint | 8 | 19 | 0 | False |
LowerBound | real | 4 | 24 | 0 | False |
UpperBound | real | 4 | 24 | 0 | False |
Comparator | tinyint | 1 | 3 | 0 | False |
Consultation¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Consultation_ID | bigint | 8 | 19 | 0 | False |
Registration_ID | bigint | 8 | 19 | 0 | False |
ConsultationDate | datetime | 8 | 23 | 3 | False |
DataDictionary¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Table | varchar | 1000 | 0 | 0 | True |
Type | varchar | 255 | 0 | 0 | True |
Code | varchar | 255 | 0 | 0 | True |
Description | varchar | 1000 | 0 | 0 | True |
ICD10Dictionary¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Code | varchar | 4 | 0 | 0 | False |
CodeDescription | varchar | 500 | 0 | 0 | False |
ParentCode | char | 3 | 0 | 0 | False |
ParentCodeDescription | varchar | 500 | 0 | 0 | False |
MedicationDictionary¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
MultilexDrug_ID | varchar | 767 | 0 | 0 | True |
ProductId | bigint | 8 | 19 | 0 | False |
FullName | varchar | 1000 | 0 | 0 | True |
RootName | varchar | 100 | 0 | 0 | True |
PackDescription | varchar | 50 | 0 | 0 | True |
Form | varchar | 50 | 0 | 0 | True |
Strength | varchar | 500 | 0 | 0 | True |
CompanyName | varchar | 200 | 0 | 0 | True |
DMD_ID | varchar | 50 | 0 | 0 | True |
MedicationIssue¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Consultation_ID | bigint | 8 | 19 | 0 | False |
MedicationIssue_ID | bigint | 8 | 19 | 0 | False |
RepeatMedication_ID | bigint | 8 | 19 | 0 | False |
MultilexDrug_ID | varchar | 255 | 0 | 0 | False |
Dose | varchar | 255 | 0 | 0 | False |
Quantity | varchar | 255 | 0 | 0 | False |
StartDate | datetime | 8 | 23 | 3 | False |
EndDate | datetime | 8 | 23 | 3 | False |
MedicationStatus | int | 4 | 10 | 0 | False |
ConsultationDate | datetime | 8 | 23 | 3 | False |
MedicationRepeat¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Consultation_ID | bigint | 8 | 19 | 0 | False |
MedicationRepeat_ID | bigint | 8 | 19 | 0 | False |
MultilexDrug_ID | varchar | 255 | 0 | 0 | False |
Dose | varchar | 255 | 0 | 0 | False |
Quantity | varchar | 255 | 0 | 0 | False |
StartDate | datetime | 8 | 23 | 3 | False |
EndDate | datetime | 8 | 23 | 3 | False |
MedicationStatus | int | 4 | 10 | 0 | False |
ConsultationDate | datetime | 8 | 23 | 3 | False |
MedicationSensitivity¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | int | 4 | 10 | 0 | False |
Consultation_ID | bigint | 8 | 19 | 0 | False |
MedicationSensitivity_ID | int | 4 | 10 | 0 | False |
MultilexDrug_ID | varchar | 100 | 0 | 0 | False |
StartDate | datetime | 8 | 23 | 3 | False |
FormulationSpecific | bit | 1 | 1 | 0 | False |
Ended | bit | 1 | 1 | 0 | True |
ConsultationDate | datetime | 8 | 23 | 3 | False |
Organisation¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Organisation_ID | bigint | 8 | 19 | 0 | False |
GoLiveDate | datetime | 8 | 23 | 3 | False |
STPCode | varchar | 50 | 0 | 0 | False |
MSOACode | varchar | 150 | 0 | 0 | False |
Region | varchar | 255 | 0 | 0 | False |
Patient¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
DateOfBirth | date | 3 | 10 | 0 | True |
DateOfDeath | date | 3 | 10 | 0 | True |
Sex | char | 1 | 0 | 0 | False |
PatientAddress¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
PatientAddress_ID | bigint | 8 | 19 | 0 | False |
StartDate | datetime | 8 | 23 | 3 | False |
EndDate | datetime | 8 | 23 | 3 | False |
AddressType | int | 4 | 10 | 0 | False |
RuralUrbanClassificationCode | int | 4 | 10 | 0 | False |
ImdRankRounded | int | 4 | 10 | 0 | False |
MSOACode | varchar | 150 | 0 | 0 | False |
QOFClusterReference¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
ClusterType | varchar | 50 | 0 | 0 | False |
ClusterName | varchar | 100 | 0 | 0 | False |
CTV3Code | varchar | 50 | 0 | 0 | False |
Description | varchar | 255 | 0 | 0 | False |
RegistrationHistory¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Registration_ID | bigint | 8 | 19 | 0 | False |
Organisation_ID | bigint | 8 | 19 | 0 | False |
Patient_ID | bigint | 8 | 19 | 0 | False |
StartDate | datetime | 8 | 23 | 3 | False |
EndDate | datetime | 8 | 23 | 3 | False |
UnitDictionary¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
UnitDictionary_ID | int | 4 | 10 | 0 | False |
CTV3Code | varchar | 50 | 0 | 0 | False |
Units | varchar | 50 | 0 | 0 | False |
Minimum | real | 4 | 24 | 0 | False |
Maximum | real | 4 | 24 | 0 | False |
LowerNormalBound | real | 4 | 24 | 0 | False |
UpperNormalBound | real | 4 | 24 | 0 | False |
DecimalPlaces | int | 4 | 10 | 0 | False |
Vaccination¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
Vaccination_ID | bigint | 8 | 19 | 0 | False |
VaccinationDate | datetime | 8 | 23 | 3 | False |
VaccinationName | varchar | 100 | 0 | 0 | False |
VaccinationName_ID | bigint | 8 | 19 | 0 | False |
VaccinationSchedulePart | int | 4 | 10 | 0 | False |
VaccinationReference¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
VaccinationName_ID | int | 4 | 10 | 0 | False |
VaccinationName | varchar | 100 | 0 | 0 | False |
VaccinationContent | varchar | 50 | 0 | 0 | False |
YCodeToSnomedMapping¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
YCode | varchar | 5 | 0 | 0 | False |
SctConceptId | bigint | 8 | 19 | 0 | True |
UK_Renal_Register¶
UKRR¶
ColumnType | MaxLength | Precision | Scale | IsNullable | |
---|---|---|---|---|---|
ColumnName | |||||
Patient_ID | bigint | 8 | 19 | 0 | False |
dataset | varchar | 1000 | 0 | 0 | True |
renal_centre | varchar | 1000 | 0 | 0 | True |
creat | real | 4 | 24 | 0 | True |
eGFR_ckdepi | real | 4 | 24 | 0 | True |
rrt_start | date | 3 | 10 | 0 | True |
mod_start | varchar | 1000 | 0 | 0 | True |
mod_prev | varchar | 1000 | 0 | 0 | True |