[gradebook2-dev] Grade scale percent range calculation

[Mukul Tiwari]

Hi All,


We have been using the sql (at the bottom) to add new grading scale to sakai
instance for a case where these scales will be available to existing courses
as well. The bug (so I think) that I came across when doing this was that if
these Letter grades are not an Ascending order with the adjacent percent,
 the percentage range that you see in the scale is incorrect. So for example
if I used the below sql to create a grade scale like this:

<http://goog_1459041888>
S (Satisfactory) - 70.0
U (Unsatisfactory) - 1.0
I (Incomplete) - 0.0

<http://goog_1459041888>
We end up seeing grade scale range in the UI like this:

<http://goog_1459041888>
I - 0.0-100.00
U - 69.99 - 1.0
S - 0.0 - 70.00

<http://goog_1459041888>
It seems to behave like this simply due to the fact that the data from
gb_grade_to_percent_mapping table is being returned in an ASC order for
letter_grade column. Would this be considered "as designed" or a bug? Should
the logic for calculating percent range be changed to accomodate scales
which do not have letter grade in order?

<http://goog_1459041888>

 <http://goog_1459041888>

<http://goog_1459041888>

 <http://goog_1459041888>

<http://goog_1459041888>

 <http://goog_1459041888>

<http://goog_1459041888>
----------------------------------------------------------------------

<http://goog_1459041888>
For new grading scale given

<http://goog_1459041888>

A - 89.5
B+ - 84.5
B - 79.5
C+ - 74.5
C - 69.5
D+ - 64.5
D - 59.5
F - 1
I - 0

SQL to add this is follows:


INSERT
INTO
    GB_GRADING_SCALE_T
    (
        OBJECT_TYPE_ID,
        VERSION,
        SCALE_UID,
        NAME,
        UNAVAILABLE
    )
    VALUES
    (
        0,
        0,
        'newgradescale',
        'new grade scale',
        false
    );

-----------------------------------------------------------------------
-- get the GB_GRADING_SCALE_T.ID as a result of the insert statement above
-- and substitute <GRADESCALEID> in all statements below
-----------------------------------------------------------------------

INSERT
INTO
    GB_GRADING_SCALE_PERCENTS_T
    (
        GRADING_SCALE_ID,
        PERCENT,
        LETTER_GRADE
    )
    VALUES
    (
        <GRADESCALEID>,
        0,
        'I'
    )
    ,
    (
        <GRADESCALEID>,
        1,
        'F'
    )
    ,
    (
        <GRADESCALEID>,
        59.5,
        'D-'
    )
    ,
    (
        <GRADESCALEID>,
        64.5,
        'D+'
    )
    ,
    (
        <GRADESCALEID>,
        69.5,
        'C-'
    )
    ,
    (
        <GRADESCALEID>,
        74.5,
        'C+'
    )
    ,
    (
        <GRADESCALEID>,
        79.5,
        'B-'
    )
    ,
    (
        <GRADESCALEID>,
        84.5,
        'B+'
    )
    ,
    (
        <GRADESCALEID>,
        89.5,
        'A'
    );


-----------------------------------------------------------------------

INSERT
INTO
    GB_GRADING_SCALE_GRADES_T
    (
        GRADING_SCALE_ID,
        LETTER_GRADE,
        GRADE_IDX
    )
    VALUES
    (
        <GRADESCALEID>,
        'A',
        0
    )
    ,
    (
        <GRADESCALEID>,
        'B+',
        1
    )
    ,
    (
        <GRADESCALEID>,
        'B-',
        2
    )
    ,
    (
        <GRADESCALEID>,
        'C+',
        3
    )
    ,
    (
        <GRADESCALEID>,
        'C-',
        4
    )
    ,
    (
        <GRADESCALEID>,
        'D+',
        5
    )
    ,
    (
        <GRADESCALEID>,
        'D-',
        6
    )
    ,
    (
        <GRADESCALEID>,
        'F',
        7
    )
    ,
    (
        <GRADESCALEID>,
        'I',
        8
    );


-----------------------------------------------------------------------


DELETE
FROM
    GB_GRADE_TO_PERCENT_MAPPING_T;

-----------------------------------------------------------------------

INSERT
INTO
    GB_GRADE_MAP_T
    (
        OBJECT_TYPE_ID,
        VERSION,
        GRADEBOOK_ID,
        GB_GRADING_SCALE_T
    )
SELECT DISTINCT
    0,0,
    GB_GRADEBOOK_T.ID,
    GB_GRADING_SCALE_T.ID
FROM
    GB_GRADEBOOK_T,
    GB_GRADING_SCALE_T
WHERE
    (
        GB_GRADEBOOK_T.ID, GB_GRADING_SCALE_T.ID
    )
    NOT IN
    (
        SELECT DISTINCT
            GRADEBOOK_ID,
            GB_GRADING_SCALE_T
        FROM
            GB_GRADE_MAP_T
        WHERE
            GB_GRADING_SCALE_T=<GRADESCALEID>
    )
AND GB_GRADING_SCALE_T.ID = <GRADESCALEID>;

-----------------------------------------------------------------------

INSERT
INTO
    GB_GRADE_TO_PERCENT_MAPPING_T
SELECT DISTINCT
    GB_GRADE_MAP_T.id,
    GB_GRADING_SCALE_PERCENTS_T.percent,
    GB_GRADING_SCALE_PERCENTS_T.letter_grade
FROM
    GB_GRADE_MAP_T,
    GB_GRADING_SCALE_T,
    GB_GRADING_SCALE_PERCENTS_T
WHERE
    GB_GRADING_SCALE_T.id=gb_grade_map_t.gb_grading_scale_t
AND GB_GRADING_SCALE_PERCENTS_T.grading_scale_id= GB_GRADING_SCALE_T.ID;





Thanks,
Mukul Tiwari
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://collab.sakaiproject.org/pipermail/gradebook2-dev/attachments/20110830/f0957ad9/attachment.html