Stored Procedure - usp_FactActiveStudentGrades

Property	Value
Name	usp_FactActiveStudentGrades
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
ST_ACDMC_HIST_A	Table
UTL_CODE_TABLE	Table
UTL_CODE_TABLE_GENERIC	Table

Name	Type
UTL_CODE_TABLE	TABLE
UTL_CODE_TABLE_GENERIC	TABLE
ST_ACDMC_HIST_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactActiveStudentGrades] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
/* 2015-09-24 Sophia Cowan - Changed to always do a Full Load, since Academic History can be deleted */

CREATE PROC [dbo].[usp_FactActiveStudentGrades]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
          CAST('aaa' AS varchar(9)) AS StudentId
        , CAST('aaa' AS varchar(6)) AS Term
        , CAST(123 AS int) AS RefNum
        , CAST(1 as int) as GradeSuccess
        , CAST(1 as int) as GradeMarginal
        , CAST(1 as int) as GradeFail
        , CAST(1 as int) as GradeWithdrawal
        , CAST(1 as int) as GradeOther
        , CAST(1 as int) as GradeA
        , CAST(1 as int) as GradeB
        , CAST(1 as int) as GradeC
        , CAST(1 as int) as GradeD
        , CAST(1 as int) as GradeF
        , CAST(1 as int) as GradeW
        , CAST(1 as int) as GradeS
        , CAST(1 as int) as GradeP
        , CAST(1 as int) as GradeU
        , CAST(1 as int) as GradeN
        , CAST(1 as int) as GradeX
        , CAST(1 as int) as GradeI
        , CAST(1 as int) as GradeByLetterOther
        , CAST(1 as int) as Attempt1
        , CAST(1 as int) as Attempt2
        , CAST(1 as int) as Attempt2OrMore
        , CAST(1 as int) as Attempt3OrMore
    END

-- Get grade records between the begin and end dates supplied to this routine.
SELECT   AH.[STUDENT_ID] AS StudentId
        ,CAST(RTRIM(SUBSTRING([CLASS_KEY],12,6)) as Varchar(6)) AS Term
        ,[REF_NUM] AS RefNum
        ,(CASE
            WHEN gradeCategory.FIELD_VALUE = 'SUCCESS' THEN 1
            ELSE 0
            END) as GradeSuccess
        ,(CASE
            WHEN gradeCategory.FIELD_VALUE = 'MARGINAL' THEN 1
            ELSE 0
            END) as GradeMarginal
        ,(CASE
            WHEN gradeCategory.FIELD_VALUE = 'FAIL' THEN 1
            ELSE 0
            END) as GradeFail
        ,(CASE
            WHEN gradeCategory.FIELD_VALUE = 'WITHDRAW' THEN 1
            ELSE 0
            END) as GradeWithdrawal
        ,(CASE
            WHEN gradeCategory.FIELD_VALUE = 'OTHER' THEN 1
            ELSE 0
            END) as GradeOther
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'A' THEN 1
            ELSE 0
            END) as GradeA
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'B' THEN 1
            ELSE 0
            END) as GradeB
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'C' THEN 1
            ELSE 0
            END) as GradeC
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'D' THEN 1
            ELSE 0
            END) as GradeD
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'F' THEN 1
            ELSE 0
            END) as GradeF
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'W' THEN 1
            ELSE 0
            END) as GradeW
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'S' THEN 1
            ELSE 0
            END) as GradeS
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'P' THEN 1
            ELSE 0
            END) as GradeP
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'U' THEN 1
            ELSE 0
            END) as GradeU
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'N' THEN 1
            ELSE 0
            END) as GradeN
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'X' THEN 1
            ELSE 0
            END) as GradeX
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'I' THEN 1
            ELSE 0
            END) as GradeI
        ,(CASE
            WHEN SUBSTRING(GRADE,1,1) = 'A' OR SUBSTRING(GRADE,1,1) = 'B' OR SUBSTRING(GRADE,1,1) = 'C' OR
                 SUBSTRING(GRADE,1,1) = 'D' OR SUBSTRING(GRADE,1,1) = 'F' OR SUBSTRING(GRADE,1,1) = 'W' OR
                 SUBSTRING(GRADE,1,1) = 'S' OR SUBSTRING(GRADE,1,1) = 'P' OR SUBSTRING(GRADE,1,1) = 'U' OR
                 SUBSTRING(GRADE,1,1) = 'N' OR SUBSTRING(GRADE,1,1) = 'X' OR SUBSTRING(GRADE,1,1) = 'I' THEN 0
            ELSE 1
            END) as GradeByLetterOther
        ,CASE
            WHEN outAH.CourseAttempt = 1 THEN 1
            ELSE 0
         END AS Attempt1
        ,CASE
            WHEN outAH.CourseAttempt = 2 THEN 1
            ELSE 0
         END AS Attempt2
        ,CASE
            WHEN outAH.CourseAttempt >= 2 THEN 1
            ELSE 0
         END AS Attempt2OrMore
        ,CASE
            WHEN outAH.CourseAttempt >= 3 THEN 1
            ELSE 0
         END AS Attempt3OrMore
FROM [dbo].[ST_ACDMC_HIST_A] AH
/* Get Grade Rank Table record */
LEFT OUTER JOIN UTL_CODE_TABLE graderank
    on (
        graderank.TABLE_NAME = 'GRADE-RANK' AND
        graderank.CODE = AH.GRADE AND
        graderank.[STATUS] = 'A'
        )
/* Get grade rank table attribute *//
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC gradeCategory
   ON (
        graderank.ISN_UTL_CODE_TABLE = gradeCategory.ISN_UTL_CODE_TABLE
        AND gradeCategory.cnxarraycolumn =     4/* Fifth sub-attribute of table */
      )
/* Determine attempt number */
LEFT OUTER JOIN
    (
        SELECT
            ROW_NUMBER() OVER (
                PARTITION BY STUDENT_ID, SUBSTRING(CLASS_KEY,2,10)
                Order by STUDENT_ID, SUBSTRING(CLASS_KEY,2,10), SUBSTRING(CLASS_KEY,12,6), SUBSTRING(SESS_END_BEG_DT,9,8), REF_NUM
                ) AS CourseAttempt
            ,STUDENT_ID
            ,REF_NUM AS RefNum
        FROM ST_ACDMC_HIST_A
    ) outAH
    ON
    (
        AH.STUDENT_ID = outAH.STUDENT_ID
        AND AH.REF_NUM = outAH.RefNum
    )
    WHEREAH.[REF_NUM] IS NOT NULL
        AND AH.[REF_NUM] != 0