...
Data about major and degree pursued comes from the hawaii.edu:menu:sis:curricula groups in Automatic Groups UH Group Store. These groups get their data from Banner's SGBSTDN table, where there is an effective term column which roughly equates to the semester when the student began in the program. However, there isn't a column that tells us when the student is no longer in the major. We need to figure that out based on other information:
...
That last situation, student changing majors, actually does provide us SGBSTDN with the semester where one major ends and the new major begins. For all other scenarios, we cannot rely on SGBSTDN to tell us when the person graduated or dropped out. SGBSTDN could easily has have hundreds of thousands of active records for people who started on a major several years ago, but who are no longer at the University.
Step 1: Find SGBSTDN that are relevant for
...
the desired term
The first challenge is to figure out which SGBSTDN records are applicable to the desired term:
...
The above is a composite group created under Automatic Groups UH Group Store upon request of an academic list. For example, here is the composite group for Spring 2014 Art BA majors:
...
The composite group described above is already intersecting with recent registration activity by setting a "floor" term (greater than or equal to 201410, Fall 2013). If there is no registration activity since this floor term, that student will not be included in the list for the major. This is the reason we do not see any majors prior to Spring 2009 in the example above. Even though there are active SGBSTDN records for majors that started in 2008, 2007, 2006 and so on, the lack of registration activity by that population allows us to keep them out of recent terms.
We can also use registration activity in lieu of graduation of approved leave data. Since a person can be on approved leave for two consecutive semesters, we could set this floor (registration) term to two terms prior to the desired term. However, we also need to detect that the person has graduated, so we need to detect lack of registration activity for the term after they have graduated. That means we would need to set the floor term to three terms prior to the desired term.
...