Sections:
academic_year academic_term academic_session event_id   event_sub_type section 
------------- ------------- ---------------- ---------- -------------- ------- 
2000          FALL          UNDG             ACCT304    LEC            A       
2000          FALL          UNDG             CRJS304    LEC            A       
2000          FALL          UNDG             POLS304    LEC            A       
2000          FALL          UNDG             SOCI304    LEC            A       

CrossListed:
ACADEMIC_YEAR ACADEMIC_TERM ACADEMIC_SESSION EVENT_ID   EVENT_SUB_TYPE SECTION CROSS_ID   CROSS_SECTION 
------------- ------------- ---------------- ---------- -------------- ------- ---------- ------------- 
2000          FALL          UNDG             CRJS304    LEC            A       POLS304    A             
2000          FALL          UNDG             POLS304    LEC            A       CRJS304    A             
2000          FALL          UNDG             SOCI304    LEC            A       CRJS304    A              

SectionPersonnel
ACADEMIC_YEAR ACADEMIC_TERM ACADEMIC_SESSION EVENT_ID   EVENT_SUB_TYPE SECTION PERSON_CODE_ID PERCENTAGE 
------------- ------------- ---------------- ---------- -------------- ------- -------------- ---------- 
2000          FALL          UNDG             CRJS304    LEC            A       P000000126     100.000    
2000          FALL          UNDG             POLS304    LEC            A       P000000126     100.000    
2000          FALL          UNDG             SOCI304    LEC            A       P000000126     100.000    

As you can see the cross-listed table is not exactly accurate, but it should have an entry for each crosslisting. So, POLS304 should have two entries for CRJS and SOCI, I'm working on getting the data corrected.


The question is, assuming that the data was in correctly...or better yet, even if it wasn't, which is always possible, what would be the best way to pull out a faculty member's actual work load. Now if I join for the faculty member's workload, I'll get information counted twice (or n times) because the cross-listed courses are actually listed as separate sections.


After being stumped on writing a straight query for it, I looked at writing an SP that would go through and create a separate table and pull out the cross-listed classes, but couldn't get it to work correctly yet. Also, thought there might be a more simple and quicker way to do it.


So in the end I would like to have a person and the courses they're teaching with only one course listed for all the cross-listed courses (at this point it doesn't matter what that representative course is).

Hope this is a worthy stumping question.