|
|
||||||
|
|
||||||
User Defined Variables
| Name | Value | Expression | Description |
EnterpriseDW |
|||
-- 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'
|
"
-- 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 |
||
exec usp_FactActiveStudentGrades 'Jan 1 1900 12:00AM' , 'Mar 4 2016 10:33AM' , 'Y' |
"exec usp_FactActiveStudentGrades '" + @[User::IncrementalBeginDtTm] + "' , '" + @[User::IncrementalEndDtTm] + "' , '" + @[User::IsFullLoad] + "'" |
||
Jan 1 1900 12:00AM |
|||
Mar 4 2016 10:33AM |
|||
Y |
|||
UPDATE EnterpriseDW.dbo.FactStudentGrades
SET StudentSK = obs_std.NewStudentSK
FROM EnterpriseDW.dbo.FactStudentGrades fct
INNER JOIN EnterpriseDW.dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK);
|
"
UPDATE " + @[User::EDWDBName] + ".dbo.FactStudentGrades
SET StudentSK = obs_std.NewStudentSK
FROM " + @[User::EDWDBName] + ".dbo.FactStudentGrades fct
INNER JOIN " + @[User::EDWDBName] + ".dbo.DimStudent ds
ON (fct.StudentSK = ds.StudentSK)
INNER JOIN dbo.Obsolete_DimStudent obs_std
ON (obs_std.OldStudentSK = ds.StudentSK);
"
|
System Variables
| Name | Value | Expression | Description |
| Powered by BI Documenter |
