Adding new teaching data - faculty_courses

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 = 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:

  • First join with name source lookup on emplid: update temp_fac_years a join _name_source_lookup b using(emplid) set a.person_id = b.person_id
  • For the remaining faculty which did not match on EMLOYEE_ID do name matching - in name matching ensure that one name of new faculty_courses is matched with only a single name of _name_source_lookup and vice versa.
  • For people who still do not match (new faculty etc), assign them new PERSON_IDs by inserting name, emplid in the PEOPLE table and then getting the new PERSON_IDs from people table directly. Should run the meta_table update scripts after this.

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 = 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(, ', ', ','), ',', ', '), '-', ' ') 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(, ', ', ','), ',', ', ')

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 != ''