| Value |
 Expression: "
-- 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
-- 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_ARCH_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_ARCH_A
) outAH
ON
(
AH.STUDENT_ID = outAH.STUDENT_ID
AND AH.REF_NUM = outAH.RefNum
)
WHERE AH.[REF_NUM] IS NOT NULL
AND AH.[REF_NUM] != 0
AND AH.[RowUpdatedOn] > 'Jan 1 1900 12:00AM'
AND AH.[RowUpdatedOn] <= 'Mar 4 2016 10:33AM'
|
| Expression |
"
-- 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 |
Properties
