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
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
|
CREATE TABLE IF NOT EXISTS gso_grad_supr (
term char(4) NOT NULL DEFAULT '',
uw_id int(8) UNSIGNED NOT NULL DEFAULT 0,
plan varchar(10) NOT NULL DEFAULT '',
wdraw_rsn varchar(4) NOT NULL DEFAULT '',
admit_term char(4) NOT NULL DEFAULT '',
CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009');
INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009');
CREATE TABLE IF NOT EXISTS grad_degree (
student_id int(8) UNSIGNED NOT NULL,
plan varchar(10) NOT NULL,
admit_term char(4) NOT NULL,
wdraw_rsn varchar(4) NOT NULL DEFAULT '',
ofis_deg_status varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
ELSE 'Not Completed'
END) VIRTUAL,
deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
INSERT IGNORE grad_degree (
student_id,
plan,
admit_term,
wdraw_rsn,
deg_start_term,
deg_as_of_term
)
SELECT
ggs.uw_id AS c_student_id,
ggs.plan,
ggs.admit_term,
ggs.wdraw_rsn,
IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term,
ggs.term AS c_as_of_term
FROM gso_grad_supr AS ggs
LEFT OUTER JOIN
grad_degree AS gd
ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
WHERE
ggs.term = 1031 AND
gd.student_id IS NULL
;
UPDATE grad_degree AS gd
INNER JOIN
gso_grad_supr AS ggs
ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
SET
gd.wdraw_rsn = ggs.wdraw_rsn,
gd.deg_as_of_term = 1035
WHERE
gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND
ggs.term = 1035
;
drop table grad_degree;
CREATE TABLE IF NOT EXISTS grad_degree (
student_id int(8) UNSIGNED NOT NULL,
plan varchar(10) NOT NULL,
admit_term char(4) NOT NULL,
wdraw_rsn varchar(4) NOT NULL DEFAULT '',
ofis_deg_status varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
ELSE 'Not Completed'
END) VIRTUAL,
ofis_deg_status2 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress2'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2'
ELSE 'Not Completed2'
END) VIRTUAL,
ofis_deg_status3 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress3'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3'
ELSE 'Not Completed3'
END) VIRTUAL,
ofis_deg_status4 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress4'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4'
ELSE 'Not Completed4'
END) VIRTUAL,
ofis_deg_status5 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress5'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5'
ELSE 'Not Completed5'
END) VIRTUAL,
ofis_deg_status6 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress6'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6'
ELSE 'Not Completed6'
END) VIRTUAL,
ofis_deg_status7 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress7'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7'
ELSE 'Not Completed7'
END) VIRTUAL,
ofis_deg_status8 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress8'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8'
ELSE 'Not Completed8'
END) VIRTUAL,
deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
INSERT IGNORE grad_degree (
student_id,
plan,
admit_term,
wdraw_rsn,
deg_start_term,
deg_as_of_term
)
SELECT
ggs.uw_id AS c_student_id,
ggs.plan,
ggs.admit_term,
ggs.wdraw_rsn,
IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term,
ggs.term AS c_as_of_term
FROM gso_grad_supr AS ggs
LEFT OUTER JOIN
grad_degree AS gd
ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
WHERE
ggs.term = 1031 AND
gd.student_id IS NULL
;
UPDATE grad_degree AS gd
INNER JOIN
gso_grad_supr AS ggs
ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
SET
gd.wdraw_rsn = ggs.wdraw_rsn,
gd.deg_as_of_term = 1035
WHERE
gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND
ggs.term = 1035
;
ALTER TABLE grad_degree DROP INDEX grad_degree_wdraw_rsn_ndx;
ALTER TABLE grad_degree DROP COLUMN deg_start_term;
SHOW CREATE TABLE grad_degree;
Table Create Table
grad_degree CREATE TABLE `grad_degree` (
`student_id` int(8) unsigned NOT NULL,
`plan` varchar(10) NOT NULL,
`admit_term` char(4) NOT NULL,
`wdraw_rsn` varchar(4) NOT NULL DEFAULT '',
`ofis_deg_status` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
ELSE 'Not Completed'
END) VIRTUAL,
`ofis_deg_status2` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress2'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2'
ELSE 'Not Completed2'
END) VIRTUAL,
`ofis_deg_status3` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress3'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3'
ELSE 'Not Completed3'
END) VIRTUAL,
`ofis_deg_status4` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress4'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4'
ELSE 'Not Completed4'
END) VIRTUAL,
`ofis_deg_status5` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress5'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5'
ELSE 'Not Completed5'
END) VIRTUAL,
`ofis_deg_status6` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress6'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6'
ELSE 'Not Completed6'
END) VIRTUAL,
`ofis_deg_status7` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress7'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7'
ELSE 'Not Completed7'
END) VIRTUAL,
`ofis_deg_status8` varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress8'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8'
ELSE 'Not Completed8'
END) VIRTUAL,
`deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term',
PRIMARY KEY (`student_id`,`plan`,`admit_term`),
KEY `grad_degree_as_of_term_ndx` (`deg_as_of_term`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
DROP TABLE grad_degree;
CREATE TABLE IF NOT EXISTS grad_degree (
student_id int(8) UNSIGNED NOT NULL,
plan varchar(10) NOT NULL,
admit_term char(4) NOT NULL,
wdraw_rsn varchar(4) NOT NULL DEFAULT '',
ofis_deg_status varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed'
ELSE 'Not Completed'
END) VIRTUAL,
ofis_deg_status2 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress2'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2'
ELSE 'Not Completed2'
END) VIRTUAL,
ofis_deg_status3 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress3'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3'
ELSE 'Not Completed3'
END) VIRTUAL,
ofis_deg_status4 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress4'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4'
ELSE 'Not Completed4'
END) VIRTUAL,
ofis_deg_status5 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress5'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5'
ELSE 'Not Completed5'
END) VIRTUAL,
ofis_deg_status6 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress6'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6'
ELSE 'Not Completed6'
END) VIRTUAL,
ofis_deg_status7 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress7'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7'
ELSE 'Not Completed7'
END) VIRTUAL,
ofis_deg_status8 varchar(15) AS (
CASE
WHEN wdraw_rsn = '' THEN 'In progress8'
WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8'
ELSE 'Not Completed8'
END) VIRTUAL,
deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data',
deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term',
CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn);
ALTER TABLE grad_degree DROP COLUMN ofis_deg_status2, DROP COLUMN ofis_deg_status3,
DROP COLUMN ofis_deg_status4, DROP COLUMN ofis_deg_status5, DROP COLUMN ofis_deg_status6,
DROP COLUMN ofis_deg_status7, DROP COLUMN ofis_deg_status8;
CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term);
INSERT IGNORE grad_degree (
student_id,
plan,
admit_term,
wdraw_rsn,
deg_start_term,
deg_as_of_term
)
SELECT
ggs.uw_id AS c_student_id,
ggs.plan,
ggs.admit_term,
ggs.wdraw_rsn,
IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term,
ggs.term AS c_as_of_term
FROM gso_grad_supr AS ggs
LEFT OUTER JOIN
grad_degree AS gd
ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
WHERE
ggs.term = 1031 AND
gd.student_id IS NULL
;
UPDATE grad_degree AS gd
INNER JOIN
gso_grad_supr AS ggs
ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term )
SET
gd.wdraw_rsn = ggs.wdraw_rsn,
gd.deg_as_of_term = 1035
WHERE
gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND
ggs.term = 1035
;
select * from grad_degree;
student_id plan admit_term wdraw_rsn ofis_deg_status deg_start_term deg_as_of_term
2 CSM 1009 ACAD Not Completed 1009 1035
select * from gso_grad_supr;
term uw_id plan wdraw_rsn admit_term
1031 2 CSM 1009
1035 2 CSM ACAD 1009
drop table grad_degree;
drop table gso_grad_supr;
|
