How do I add Grade to a SQL Report

Question

I have a sql report of Students and I would like to add a grade for them. How do I do this properly in SQL?

Answer 1

Step 1: Add a Transition Date Variable:

DECLARE @TransitionDate DATE = ( SELECT AV.ValueAsDateTime FROM [AttributeValue] AV 
                                                            INNER JOIN Attribute A ON A.Id = AV.AttributeId 
                                                            WHERE A.[Key] = 'GradeTransitionDate'
                                                            )

Step 2: Join to the Grade Defined Value and use the 'GetGrade' function with the person's graduation year and the Transition Date variable to calculate the correct grade:

LEFT JOIN DefinedValue DV ON DV.[Value] = [dbo].[ufnCrm_GetGradeOffset](P.GraduationYear, @TransitionDate) AND DV.DefinedTypeId = 51

Example (Central AZ) New Student Report: https://rock.centralaz.com/page/2532?NewStudentsOnly=True