Here a schema, there a schema, everywhere a bad schema

This sql query will create a temporary table that will contain the whatever (gradebook_translation_pk1) for any schema that does not mean Academic Affairs standards. It seemed necessary to put the upper and lower bounds on separate lines. We join the gradebook symbol table (each GT entries should have 10 symbols, one for each letter combination), and produce a list of bad GTs based on no having correct upper or lower boundaries. There are lots of them.

declare @temporaryGT table
 (
 badGT int
 )
 insert into @temporaryGT (badGT)
 select distinct gradebook_translator_pk1 from gradebook_symbol where
 (
 (symbol='A' and upper_bound != 100)
 or (symbol='A' and lower_bound != 92.945)
 or (symbol='A-' and upper_bound != 92.945)
 or (symbol='A-' and lower_bound != 89.945 )
 or (symbol='B+' and upper_bound != 89.945)
 or (symbol='B+' and lower_bound != 86.945 )
 or (symbol='B' and upper_bound != 86.945)
 or (symbol='B' and lower_bound != 82.945 )
 or (symbol='B-' and upper_bound != 82.945)
 or (symbol='B-' and lower_bound != 79.945 )
 or (symbol='C+' and upper_bound != 79.945)
 or (symbol='C+' and lower_bound != 76.945 )
 or (symbol='C' and upper_bound != 76.945)
 or (symbol='C' and lower_bound != 72.945 )
 or (symbol='C-' and upper_bound != 72.945)
 or (symbol='C-' and lower_bound != 69.945 )
 or (symbol='D' and upper_bound != 69.945)
 or (symbol='D' and lower_bound != 59.945 )
 or (symbol='F' and upper_bound != 59.945)
 or (symbol='F' and lower_bound != 0 )
 or (Symbol='A+')
 or (Symbol='D+')
 or (Symbol='D-')
 )

Now – select relevant data from other tables (such as course_main) by joining the GT crsmain_pk1 on course_main.pk1 – then we can get a list of courses with bad schemas. If we do it that way, we get the same course several times, so we might want to use distinct to get a list of problematic courses

SELECT cm.course_id
 FROM [BBLEARN].[dbo].[GRADEBOOK_TRANSLATOR] gt
 join course_main cm on cm.pk1 = gt.crsmain_pk1
 where gt.pk1 in (select badGT from @temporaryGT)
 and cm.course_id like '%12SP'

Currently, this returns 187 hits with 165 distinct courses. Some courses are selected twice with different schema titles which is easiest to see with an _order by course_id clause at the end, and the select line modified to be ‘select cm.course_id, gt.title.

BMS16201_12SP    University Schema
BMS16201_12SP    University Grading Schema Rounded Up

A title called “University Schema” could be in various times with different GT.pk1 – and they are all ‘bad’

Advertisements
This entry was posted in blackboard, sql. Bookmark the permalink.