DWStaging
 dbo.arch_StudentClassAcademicHistory (View)
Home  Diagrams  Documents  Images  DWStaging  Storage  Users  Roles  Schemas  Tables  Views  Indexes  Triggers  Stored Procedures  Functions  Data Types  XML Schemas  Rules  Defaults  Fulltext Catalogs  Synonyms  Keys  Partitions  Assemblies 
Current Path:  DWStaging   Views   dbo.arch_StudentClassAcademicHistory
  Properties
Property Value
Name arch_StudentClassAcademicHistory
Schema dbo
Quoted Identifier Status True
Ansi Nulls Status True
Returns View Metadata False
Create Date 10/3/2014 1:28 PM
Description
  Columns
Column Name
Data Type
Allow Nulls
Fulltext Indexed
Identity
Description / Computed Formula
varchar(1)
True
False
False
varchar(4)
True
False
False
varchar(3)
True
False
False
varchar(10)
True
False
False
date(10, 0)
True
False
False
varchar(8)
True
False
False
varchar(12)
True
False
False
varchar(8)
True
False
False
varchar(1)
True
False
False
varchar(4)
True
False
False
varchar(25)
True
False
False
varchar(18)
True
False
False
varchar(3)
True
False
False
varchar(1)
True
False
False
varchar(20)
True
False
False
numeric(17, 2)
False
False
False
varchar(2)
True
False
False
varchar(10)
True
False
False
varchar(20)
True
False
False
numeric(17, 2)
False
False
False
varchar(10)
True
False
False
varchar(2)
True
False
False
varchar(30)
True
False
False
varchar(10)
True
False
False
varchar(6)
True
False
False
varchar(2)
True
False
False
varchar(30)
True
False
False
varchar(51)
False
False
False
varchar(1)
True
False
False
varchar(8)
True
False
False
varchar(32)
True
False
False
varchar(14)
False
False
False
varchar(1)
True
False
False
varchar(20)
True
False
False
date(10, 0)
True
False
False
varchar(8)
True
False
False
varchar(12)
True
False
False
varchar(8)
True
False
False
int(10, 0)
True
False
False
varchar(25)
True
False
False
date(10, 0)
True
False
False
varchar(4)
True
False
False
date(10, 0)
True
False
False
varchar(30)
True
False
False
varchar(2)
True
False
False
varchar(30)
True
False
False
varchar(2)
True
False
False
varchar(9)
True
False
False
varchar(6)
True
False
False
date(10, 0)
True
False
False
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
  Parent Dependencies (objects that dbo.arch_StudentClassAcademicHistory depends on)
Name Type
Table
Table
Table
  Child Dependencies (objects that depend on dbo.arch_StudentClassAcademicHistory)
Name Type
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: View [dbo].[arch_StudentClassAcademicHistory] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_StudentClassAcademicHistory]
AS
    SELECT  
             SUBSTRING(AH_CRS_CNTR_SECT, 1, 10) AS AcademicHistCourseId
            ,SUBSTRING(AH_CRS_CNTR_SECT, 11, 1) AS AcademicHistCampusId
            ,SUBSTRING(AH_CRS_CNTR_SECT, 12, 4) AS AcademicHistCenterId 
            ,SUBSTRING(AH_CRS_CNTR_SECT, 16, 3) AS AcademicHistClassSection
            ,CLASS_KEY AS ClassKey
            ,SUBSTRING(CLASS_KEY, 1, 1) AS ClassType 
            ,CAST(
              CASE
               WHEN SUBSTRING(CLASS_KEY, 1, 1) = 'B' THEN 'Credit Transfer'
               WHEN SUBSTRING(CLASS_KEY, 1, 1) = 'C' THEN 'Credit'
               WHEN SUBSTRING(CLASS_KEY, 1, 1) = 'N' THEN 'Non-Credit'
               WHEN SUBSTRING(CLASS_KEY, 1, 1) = 'M' THEN 'Non-Credit Transfer'
               ELSE 'Unknown'
              END AS VARCHAR(20)) AS ClassTypeDesc
            ,SUBSTRING(CLASS_KEY, 2, 10) AS CourseId
            ,SUBSTRING(CLASS_KEY, 12, 6) AS EffectiveTerm
            ,SUBSTRING(CLASS_KEY, 18, 1) AS CampusId
            ,SUBSTRING(CLASS_KEY, 19, 4) AS CenterId
            ,SUBSTRING(CLASS_KEY, 23, 3) AS ClassSection 
            ,CASE
               WHEN CLS_OPTN = 'H' THEN 'Honors'
               WHEN CLS_OPTN = 'W' THEN 'Writing'
               WHEN CLS_OPTN = 'B' THEN 'Honors and Writing'
               ELSE CLS_OPTN
             END AS ClassOption 
            ,CRED_TY AS CreditType
            ,CredTy.DESCRIPTION AS CreditTypeDescription
            ,CRNT_CRS AS CurrentCourseId
            ,CRS_EXMPTN_TST_SCR CourseExemptionTestScore
            ,CRS_TTL AS CourseTitle
            ,ISNULL(EXTRNL_CRED_HRS, 0) AS  CreditHours
            ,ISNULL(EXTRNL_CNTCT_HRS, 0) AS ContactHours
            ,GRADE AS Grade
            ,Grd.DESCRIPTION AS GradeDescription
            ,GRADE_FRGVNS_CD AS GradeSpecialActionCode
            ,CASE
               WHEN GRADE_FRGVNS_CD = 'T' THEN 'Course has been repeated and is NOT the current one'
               WHEN GRADE_FRGVNS_CD = 'R' THEN 'Course has been repeated and is the current one'
               WHEN GRADE_FRGVNS_CD = 'A' THEN 'Amnesty has been posted for this class'
               WHEN GRADE_FRGVNS_CD = 'M' THEN 'Multi Credit for this Course'
               WHEN GRADE_FRGVNS_CD = 'O' THEN 'Other'
               ELSE ' '
             END AS GradeSpecialAction
            ,ST_ACDMC_HIST_A.INST_ID AS InstitutionId
            ,ST_ACDMC_HIST_A.INST_TY AS InstitutionTypeCode
            ,Inst.INST_NM AS InstitutionName 
            ,CASE
               WHEN ST_ACDMC_HIST_A.INST_TY = 'P' THEN 'Post Secondary'
               WHEN ST_ACDMC_HIST_A.INST_TY = 'H' THEN 'High School'
               WHEN ST_ACDMC_HIST_A.INST_TY = 'A' THEN 'Agency'
               ELSE 'Unknown'
             END AS InstitutionType
            ,INSTR_NM AS InstructorName
            ,REF_NUM AS ReferenceNumber
            ,CASE WHEN SUBSTRING(SESS_END_BEG_DT, 1, 8) = ' ' THEN NULL
                 ELSE CONVERT(DATE, SUBSTRING(SESS_END_BEG_DT, 1, 8))
            END AS SessionEndDate
            ,CASE WHEN SUBSTRING(SESS_END_BEG_DT, 9, 8) = ' ' THEN NULL
                 ELSE CONVERT(DATE, SUBSTRING(SESS_END_BEG_DT, 9, 8))
            END AS SessionBeginDate
            ,SESSION_CD AS SessionCode
            ,SPCL_CRED_TY AS SpecialCreditTypeCode
            ,SpclCred.DESCRIPTION AS SpecialCreditType
            ,SPCL_DSGNTR AS SpecialDesignatorCode
            ,SpclDsg.DESCRIPTION AS SpecialDesignator
            ,CASE
               WHEN SRCH_NAME = ',,' THEN ' ' 
               ELSE SRCH_NAME
             END  AS SearchName             
            ,STUDENT_ID AS StudentId
            ,CAST(CONVERT(DATE, WTHDRWL_DT) AS DATE) AS WithdrawlDate
            ,CASE
               WHEN CRED_IND = 'C' THEN 'Credit'
               WHEN CRED_IND = 'N' THEN 'Non Credit'
               ELSE CRED_IND
             END AS CreditIndicator
            ,TRM_YR_RPTD TermReported
            ,CONVERT(DATE, ST_ACDMC_HIST_A.ADD_DATE) AS AddDate
            ,SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.ADD_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.ADD_TIME),1,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.ADD_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.ADD_TIME),3,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.ADD_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.ADD_TIME),5,2) + '.' + 
               SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.ADD_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.ADD_TIME),7,1) + '00' 
               AS AddTime
            ,ST_ACDMC_HIST_A.ADD_USER_ID AS AddUserId
            ,ST_ACDMC_HIST_A.ADD_PGM AS AddProgram
            ,CONVERT(DATE, ST_ACDMC_HIST_A.MAINT_DATE) AS MaintenanceDate
            ,SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.MAINT_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.MAINT_TIME),1,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.MAINT_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.MAINT_TIME),3,2) + ':' +
               SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.MAINT_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.MAINT_TIME),5,2) + '.' + 
               SUBSTRING(REPLICATE('0',7-LEN(ST_ACDMC_HIST_A.MAINT_TIME))+CONVERT(VARCHAR,ST_ACDMC_HIST_A.MAINT_TIME),7,1) + '00' 
               AS MaintenanceTime
            ,ST_ACDMC_HIST_A.MAINT_USER_ID AS MaintenanceUserId
            ,ST_ACDMC_HIST_A.MAINT_PGM AS MaintenanceProgram
            --,CHECKSUM (*) AS RowChecksum
            --,RowUpdatedOn
    FROM    dbo.ST_ACDMC_HIST_A
    LEFT OUTER JOIN UTL_CODE_TABLE CredTy
    ON
    (
       CredTy.TABLE_NAME = 'CRED-TYPE'
       AND CredTy.CODE = CRED_TY
       AND CredTy.STATUS = 'A'
    )
    LEFT OUTER JOIN UTL_CODE_TABLE Grd
    ON
    (
       Grd.TABLE_NAME = 'GRADE'
       AND Grd.CODE = GRADE
       AND Grd.STATUS = 'A'
    )    
    LEFT OUTER JOIN UTL_CODE_TABLE SpclCred
    ON
    (
       SpclCred.TABLE_NAME = 'SPCLCREDTY'
       AND SpclCred.CODE = SPCL_CRED_TY
       AND SpclCred.STATUS = 'A'
       AND SPCL_CRED_TY IS NOT NULL
    )    
    LEFT OUTER JOIN UTL_CODE_TABLE SpclDsg
    ON
    (
       SpclDsg.TABLE_NAME = 'SPCLDSGNTR'
       AND SpclDsg.CODE = SPCL_DSGNTR
       AND SpclDsg.STATUS = 'A'
       AND SPCL_DSGNTR IS NOT NULL
    )     
    LEFT OUTER JOIN ST_INSTITUTION_A Inst
    ON
    (
       Inst.INST_TY = ST_ACDMC_HIST_A.INST_TY
       AND Inst.INST_ID = ST_ACDMC_HIST_A.INST_ID
    )   
    WHERE  STUDENT_ID IS NOT NULL 
            AND CLASS_KEY IS NOT NULL
Powered by BI Documenter