generated from NHSDigital/repository-template
-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathcreate_tables.sql
267 lines (234 loc) · 8.95 KB
/
create_tables.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
USE ServiceInsightsDB;
GO
-- Table: EPISODE
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'EPISODE'
)
BEGIN
CREATE TABLE dbo.[EPISODE]
(
EPISODE_ID BIGINT NOT NULL,
SCREENING_ID BIGINT NOT NULL,
NHS_NUMBER BIGINT NOT NULL,
EPISODE_TYPE_ID BIGINT NULL,
EPISODE_OPEN_DATE DATE NULL,
APPOINTMENT_MADE_FLAG SMALLINT NULL,
FIRST_OFFERED_APPOINTMENT_DATE DATE NULL,
ACTUAL_SCREENING_DATE DATE NULL,
EARLY_RECALL_DATE DATE NULL,
CALL_RECALL_STATUS_AUTHORISED_BY VARCHAR(200) NULL,
END_CODE_ID BIGINT NULL,
END_CODE_LAST_UPDATED DATETIME NULL,
REASON_CLOSED_CODE_ID BIGINT NULL,
FINAL_ACTION_CODE_ID BIGINT NULL,
END_POINT VARCHAR(200) NULL,
ORGANISATION_ID BIGINT NULL,
BATCH_ID VARCHAR(100) NULL,
SRC_SYS_PROCESSED_DATETIME DATETIME NULL,
EXCEPTION_FLAG SMALLINT NULL,
RECORD_INSERT_DATETIME DATETIME NULL,
RECORD_UPDATE_DATETIME DATETIME NULL,
constraint PK_EPISODE primary key (EPISODE_ID)
);
END
-- Table: END_CODE_LKP
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'END_CODE_LKP'
)
BEGIN
CREATE TABLE END_CODE_LKP
(
END_CODE_ID BIGINT not null,
LEGACY_END_CODE VARCHAR(10) null,
END_CODE VARCHAR(50) null,
END_CODE_DESCRIPTION VARCHAR(300) null,
constraint PK_END_CODE_LKP primary key (END_CODE_ID)
);
END
alter table EPISODE
add constraint FK_EPISODE_END_CODE_LKP foreign key (END_CODE_ID)
references END_CODE_LKP (END_CODE_ID)
-- Table: EPISODE_TYPE_LKP
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'EPISODE_TYPE_LKP'
)
BEGIN
CREATE TABLE EPISODE_TYPE_LKP
(
EPISODE_TYPE_ID BIGINT not null,
EPISODE_TYPE VARCHAR(10) null,
EPISODE_DESCRIPTION VARCHAR(300) null,
constraint PK_EPISODE_TYPE_LKP primary key (EPISODE_TYPE_ID)
);
END
alter table EPISODE
add constraint FK_EPISODE_EPISODE_TYPE_LKP foreign key (EPISODE_TYPE_ID)
references EPISODE_TYPE_LKP (EPISODE_TYPE_ID)
-- Table: FINAL_ACTION_CODE_LKP
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'FINAL_ACTION_CODE_LKP'
)
BEGIN
CREATE TABLE FINAL_ACTION_CODE_LKP
(
FINAL_ACTION_CODE_ID BIGINT not null,
FINAL_ACTION_CODE VARCHAR(50) not null,
FINAL_ACTION_CODE_DESCRIPTION VARCHAR(300) null,
constraint PK_FINAL_ACTION_CODE_LKP primary key (FINAL_ACTION_CODE_ID)
);
END
alter table EPISODE
add constraint FK_EPISODE_FINAL_ACTION_CODE_LKP foreign key (FINAL_ACTION_CODE_ID)
references FINAL_ACTION_CODE_LKP (FINAL_ACTION_CODE_ID)
-- Table: REASON_CLOSED_CODE_LKP
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'REASON_CLOSED_CODE_LKP'
)
BEGIN
CREATE TABLE REASON_CLOSED_CODE_LKP
(
REASON_CLOSED_CODE_ID BIGINT not null,
REASON_CLOSED_CODE VARCHAR(50) not null,
REASON_CLOSED_CODE_DESCRIPTION VARCHAR(300) null,
constraint PK_REASON_CLOSED_CODE_LKP primary key (REASON_CLOSED_CODE_ID)
);
END
alter table EPISODE
add constraint FK_EPISODE_REASON_CLOSED_CODE_LKP foreign key (REASON_CLOSED_CODE_ID)
references REASON_CLOSED_CODE_LKP (REASON_CLOSED_CODE_ID)
-- Table: PARTICIPANT_SCREENING_PROFILE
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'PARTICIPANT_SCREENING_PROFILE'
)
BEGIN
CREATE TABLE PARTICIPANT_SCREENING_PROFILE
(
NHS_NUMBER BIGINT NOT NULL,
SCREENING_NAME VARCHAR(200) NULL,
PRIMARY_CARE_PROVIDER VARCHAR(50) NULL,
PREFERRED_LANGUAGE VARCHAR(50) NULL,
REASON_FOR_REMOVAL VARCHAR(50) NULL,
REASON_FOR_REMOVAL_DT DATE NULL,
NEXT_TEST_DUE_DATE DATE NULL,
NEXT_TEST_DUE_DATE_CALC_METHOD VARCHAR(100) NULL,
PARTICIPANT_SCREENING_STATUS VARCHAR(100) NULL,
SCREENING_CEASED_REASON VARCHAR(100) NULL,
IS_HIGHER_RISK SMALLINT NULL,
IS_HIGHER_RISK_ACTIVE SMALLINT NULL,
HIGHER_RISK_NEXT_TEST_DUE_DATE DATE NULL,
HIGHER_RISK_REFERRAL_REASON_CODE VARCHAR(100) NULL,
HR_REASON_CODE_DESCRIPTION VARCHAR(200) NULL,
DATE_IRRADIATED DATE NULL,
GENE_CODE VARCHAR(100) NULL,
GENE_CODE_DESCRIPTION VARCHAR(200) NULL,
SRC_SYS_PROCESSED_DATETIME DATETIME NOT NULL,
RECORD_INSERT_DATETIME DATETIME NULL,
RECORD_UPDATE_DATETIME DATETIME NULL,
PRIMARY KEY (NHS_NUMBER, SRC_SYS_PROCESSED_DATETIME)
);
END
-- Table: PARTICIPANT_SCREENING_EPISODE
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'PARTICIPANT_SCREENING_EPISODE'
)
BEGIN
CREATE TABLE PARTICIPANT_SCREENING_EPISODE
(
EPISODE_ID BIGINT NOT NULL,
NHS_NUMBER BIGINT NOT NULL,
SCREENING_NAME VARCHAR(200) NULL,
EPISODE_TYPE VARCHAR(50) NULL,
EPISODE_TYPE_DESCRIPTION VARCHAR(300) NULL,
EPISODE_OPEN_DATE DATE NULL,
APPOINTMENT_MADE_FLAG SMALLINT NULL,
FIRST_OFFERED_APPOINTMENT_DATE DATE NULL,
ACTUAL_SCREENING_DATE DATE NULL,
EARLY_RECALL_DATE DATE NULL,
CALL_RECALL_STATUS_AUTHORISED_BY VARCHAR(200) NULL,
END_CODE VARCHAR(50) NULL,
END_CODE_DESCRIPTION VARCHAR(300) NULL,
END_CODE_LAST_UPDATED DATETIME NULL,
REASON_CLOSED_CODE VARCHAR(50) NULL,
REASON_CLOSED_CODE_DESCRIPTION VARCHAR(300) NULL,
FINAL_ACTION_CODE VARCHAR(50) NULL,
FINAL_ACTION_CODE_DESCRIPTION VARCHAR(300) NULL,
END_POINT VARCHAR(200) NULL,
ORGANISATION_CODE VARCHAR(50) NULL,
ORGANISATION_NAME VARCHAR(300) NULL,
BATCH_ID VARCHAR(100) NULL,
SRC_SYS_PROCESSED_DATETIME DATETIME NOT NULL,
RECORD_INSERT_DATETIME DATETIME NULL,
RECORD_UPDATE_DATETIME DATETIME NULL,
EXCEPTION_FLAG SMALLINT NULL,
PRIMARY KEY (EPISODE_ID, SRC_SYS_PROCESSED_DATETIME)
);
END
-- Table: ORGANISATION_LKP
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'ORGANISATION_LKP'
)
BEGIN
CREATE TABLE ORGANISATION_LKP
(
ORGANISATION_ID BIGINT NOT NULL,
CONSTRAINT PK_ORGANISATION_ID
PRIMARY KEY (ORGANISATION_ID),
SCREENING_NAME VARCHAR(50) NULL,
ORGANISATION_CODE VARCHAR(50) NULL,
ORGANISATION_NAME VARCHAR(50) NULL,
ORGANISATION_TYPE VARCHAR(50) NULL,
IS_ACTIVE VARCHAR(50) NULL,
);
END
-- Table: SCREENING_LKP
IF NOT EXISTS
(
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'SCREENING_LKP'
)
BEGIN
CREATE TABLE SCREENING_LKP
(
SCREENING_ID BIGINT NOT NULL,
CONSTRAINT PK_SCREENING_ID
PRIMARY KEY (SCREENING_ID),
SCREENING_NAME VARCHAR(100) NOT NULL,
SCREENING_TYPE VARCHAR(100) NULL,
SCREENING_ACRONYM VARCHAR(100) NULL,
SCREENING_WORKFLOW_ID VARCHAR(100) NULL,
);
END