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;
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) GENERATED ALWAYS 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',
  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;
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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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',
  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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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;
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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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) GENERATED ALWAYS 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',
  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;