Put the courses data into a new temporary table with these additional coloumns added to it : fy, school_id, department_id, person_id
1. Update FY for the year for which course data is being added.
2. Update department_id by joining with departments_aliases on department name
update temp_1 a join departments_aliases b ON a.dept = b.alias set a.department_id = b.department_id;
3. Update school_id by joining with school_aliases on school name
update temp_1 a join school_aliases b ON a.school = b.alias set a.school_id = b.school_id;
4. Hand match any remaining departments/schools, create new entries in department_aliases / departments as needed.
5. Getting PERSON_IDs - This be a three step process:
6. Final insert - make sure all the field names are aligned with the current faculty_courses table -
insert into faculty_courses
select Null, Name, Rank, Appt_Type, Appt_Aggr, Dept, Area, School, fte, On_Duty_fte, Class_ID, Class_Title, Class_Type, num_inst, (ug1enr + ug2enr + ugoenr + grenr), (ug1un+ ug2un+ ugoun), (grunits + ugoun), (aug1un + aug2un + augoun), (agrun + augoun), Class_Credit, if(class_type = 'Low Enr', 0, class_credit), ug1un/(ug1un + ug2un + ugoun + grunits), (ug1un + ug2un)/(ug1un + ug2un + ugoun + grunits), Class_Ind_Study_Credit, aug1un, aug2un, augoun, ug1enr + ug2enr + ugoenr, grenr, ugoenr, ug2un, ug1un, grunits, ugoun, ug1un + ug2un + ugoun + grunits, dist, Adj_Dist, year, school_id, department_id, person_id from temp_1
Updated tables (might) include:
update temp_1 a join _match_fac_courses b on a.name = b.name1 set a.person_id = b.id_b where a.person_id = '' and b.match = 1;
people having other emplid or having no emplids as listed in the courses table.
select * from temp_1 where person_id = '';
select emplid, name, count(*) from temp_1 where person_id = '' group by emplid, name;
check for multiple repeats of same id_a or id_b using
- select * from minerva._match_fac_courses_2 a where a.match = 1 group by id_b having count(*) > 1
update temp_1 b join _match_fac_courses_2 a on a.name1 = replace(replace(replace(b.name, ', ', ','), ',', ', '), '-', ' ') set b.person_id = a.id_b where a.match = 1 and b.person_id = ''
select name, count(*) from temp_1 b where person_id = '' group by emplid, replace(replace(b.name, ', ', ','), ',', ', ')
special processing needed for names containing a hyphen '-'
insert into people select null, replace(replace(name, ', ', ','), ',', ', '), null, emplid, null, null, null, 0, 0, null, null, null, null from temp_1 where person_id = '' and emplid not in ('#N/A', 0) group by emplid, replace(replace(name, ', ', ','), ',', ', ')
update temp_1 a join (select * from people order by person_id desc limit 366) b on a.emplid = b.employee_id set a.person_id = b.person_id where a.person_id = '' and b.person_id != 0 and b.person_id != ''