DWStaging
 dbo.arch_StudentAcademicHistory (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_StudentAcademicHistory
  Properties
Property Value
Name arch_StudentAcademicHistory
Schema dbo
Quoted Identifier Status True
Ansi Nulls Status True
Returns View Metadata False
Create Date 10/3/2014 3:43 PM
Description
  Columns
Column Name
Data Type
Allow Nulls
Fulltext Indexed
Identity
Description / Computed Formula
varchar(1)
True
False
False
varchar(1)
True
False
False
varchar(1)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
date(10, 0)
True
False
False
date(10, 0)
True
False
False
date(10, 0)
True
False
False
date(10, 0)
True
False
False
date(10, 0)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
numeric(17, 2)
True
False
False
varchar(3)
True
False
False
varchar(3)
True
False
False
varchar(3)
True
False
False
varchar(3)
True
False
False
varchar(3)
True
False
False
varchar(30)
True
False
False
varchar(30)
True
False
False
varchar(30)
True
False
False
varchar(30)
True
False
False
varchar(30)
True
False
False
varchar(4)
True
False
False
varchar(4)
True
False
False
varchar(4)
True
False
False
varchar(4)
True
False
False
varchar(4)
True
False
False
varchar(1)
True
False
False
varchar(1)
True
False
False
varchar(1)
True
False
False
varchar(1)
True
False
False
varchar(1)
True
False
False
varchar(9)
True
False
False
varchar(2)
True
False
False
varchar(254)
True
False
False
varchar(2)
True
False
False
varchar(254)
True
False
False
varchar(6)
True
False
False
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
  Parent Dependencies (objects that dbo.arch_StudentAcademicHistory depends on)
Name Type
Table
Table
Table
  Child Dependencies (objects that depend on dbo.arch_StudentAcademicHistory)
Name Type
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: View [dbo].[arch_StudentAcademicHistory] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_StudentAcademicHistory]
AS
    SELECT   AH.CLASS_TY 
            ,AH.STUDENT_ID 
            ,AH.TRM_YR 
            ,MAX(CASE TNote.cnxarraycolumn
                  WHEN 0 THEN TNote.Before_after
                  ELSE NULL
                END) AS BEFORE_AFTER1 
            ,MAX(CASE TNote.cnxarraycolumn
                  WHEN 0 THEN TRNSCRPT_CD
                  ELSE NULL
                END) AS Transcrpt_Cd1 
            ,MAX(CASE WHEN TNote.cnxarraycolumn = 0
                          AND trnscrpt_cd > ' '
                     THEN ISNULL(DESC_LINE_1, ' ') + ' ' + ISNULL(desc_line_2, ' ') + ' ' + ISNULL(desc_line_3, ' ')
                          + ' ' + ISNULL(desc_line_4, ' ') + ' ' + ISNULL(desc_line_5, ' ')
                     ELSE NULL
                END) AS Transcrpt_Cd1_Desc 
            ,MAX(CASE TNote.cnxarraycolumn
                  WHEN 1 THEN TNote.Before_after
                  ELSE NULL
                END) AS BEFORE_AFTER2 
            ,MAX(CASE TNote.cnxarraycolumn
                  WHEN 1 THEN TRNSCRPT_CD
                  ELSE NULL
                END) AS Transcrpt_Cd2 
            ,MAX(CASE TNote.cnxarraycolumn
                  WHEN 1
                     THEN ISNULL(DESC_LINE_1, ' ') + ' ' + ISNULL(desc_line_2, ' ') + ' ' + ISNULL(desc_line_3, ' ')
                          + ' ' + ISNULL(desc_line_4, ' ') + ' ' + ISNULL(desc_line_5, ' ')
                  ELSE NULL
                END) AS Transcrpt_cd2_Desc 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN PS_Awd.PS_AWD_TY
                  ELSE NULL
                END) AS PS_AWD_TY1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN PS_Awd.PS_AWD_TTL
                  ELSE NULL
                END) AS PS_AWD_TTL1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN CONVERT(DATE, PS_Awd.PS_AWD_DT)
                  ELSE NULL
                END) AS PS_AWD_DT1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN ISNULL(PS_Awd.PS_AWD_CRED_HRS, 0)
                  ELSE NULL
                END) AS PS_AWD_CRED_HRS1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN ISNULL(PS_Awd.PS_AWD_GPA, 0)
                  ELSE NULL
                END) AS PS_AWD_GPA1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN PS_Awd.PS_AWD_LVL
                  ELSE NULL
                END) AS PS_AWD_LVL1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 0 THEN PS_Awd.PS_HNRS_CD
                  ELSE NULL
                END) AS PS_HNRS_CD1 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN PS_Awd.PS_AWD_TY
                  ELSE NULL
                END) AS PS_AWD_TY2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN PS_Awd.PS_AWD_TTL
                  ELSE NULL
                END) AS PS_AWD_TTL2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN CONVERT(DATE, PS_Awd.PS_AWD_DT)
                  ELSE NULL
                END) AS PS_AWD_DT2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN ISNULL(PS_Awd.PS_AWD_CRED_HRS, 0)
                  ELSE NULL
                END) AS PS_AWD_CRED_HRS2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN ISNULL(PS_Awd.PS_AWD_GPA, 0)
                  ELSE NULL
                END) AS PS_AWD_GPA2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN PS_Awd.PS_AWD_LVL
                  ELSE NULL
                END) AS PS_AWD_LVL2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 1 THEN PS_Awd.PS_HNRS_CD
                  ELSE NULL
                END) AS PS_HNRS_CD2 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN PS_Awd.PS_AWD_TY
                  ELSE NULL
                END) AS PS_AWD_TY3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN PS_Awd.PS_AWD_TTL
                  ELSE NULL
                END) AS PS_AWD_TTL3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN CONVERT(DATE, PS_Awd.PS_AWD_DT)
                  ELSE NULL
                END) AS PS_AWD_DT3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN ISNULL(PS_Awd.PS_AWD_CRED_HRS, 0)
                  ELSE NULL
                END) AS PS_AWD_CRED_HRS3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN ISNULL(PS_Awd.PS_AWD_GPA, 0)
                  ELSE NULL
                END) AS PS_AWD_GPA3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN PS_Awd.PS_AWD_LVL
                  ELSE NULL
                END) AS PS_AWD_LVL3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 2 THEN PS_Awd.PS_HNRS_CD
                  ELSE NULL
                END) AS PS_HNRS_CD3 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN PS_Awd.PS_AWD_TY
                  ELSE NULL
                END) AS PS_AWD_TY4 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN PS_Awd.PS_AWD_TTL
                  ELSE NULL
                END) AS PS_AWD_TTL4 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN CONVERT(DATE, PS_Awd.PS_AWD_DT)
                  ELSE NULL
                END) AS PS_AWD_DT4 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN ISNULL(PS_Awd.PS_AWD_CRED_HRS, 0)
                  ELSE NULL
                END) AS PS_AWD_CRED_HRS4 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN ISNULL(PS_Awd.PS_AWD_GPA, 0)
                  ELSE NULL
                END) AS PS_AWD_GPA4 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN PS_Awd.PS_AWD_LVL
                  ELSE NULL
                END) AS PS_AWD_LVL4 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 3 THEN PS_Awd.PS_HNRS_CD
                  ELSE NULL
                END) AS PS_HNRS_CD4
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN PS_Awd.PS_AWD_TY
                  ELSE NULL
                END) AS PS_AWD_TY5 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN PS_Awd.PS_AWD_TTL
                  ELSE NULL
                END) AS PS_AWD_TTL5 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN CONVERT(DATE, PS_Awd.PS_AWD_DT)
                  ELSE NULL
                END) AS PS_AWD_DT5 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN ISNULL(PS_Awd.PS_AWD_CRED_HRS, 0)
                  ELSE NULL
                END) AS PS_AWD_CRED_HRS5 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN ISNULL(PS_Awd.PS_AWD_GPA, 0)
                  ELSE NULL
                END) AS PS_AWD_GPA5 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN PS_Awd.PS_AWD_LVL
                  ELSE NULL
                END) AS PS_AWD_LVL5 
            ,MAX(CASE PS_Awd.cnxarraycolumn
                  WHEN 4 THEN PS_Awd.PS_HNRS_CD
                  ELSE NULL
                END) AS PS_HNRS_CD5 
    FROM    dbo.ST_ACDMC_HIST_A_POST_SECONDARY_AWARD AS PS_Awd
            RIGHT OUTER JOIN dbo.ST_ACDMC_HIST_A AS AH ON PS_Awd.ISN_ST_ACDMC_HIST_A = AH.ISN_ST_ACDMC_HIST_A
            LEFT OUTER JOIN dbo.ST_ACDMC_HIST_A_TRNSCRPT_NOTE AS TNote ON AH.ISN_ST_ACDMC_HIST_A = TNote.ISN_ST_ACDMC_HIST_A
    WHERE   AH.CLASS_TY IS NOT NULL 
            AND AH.STUDENT_ID IS NOT NULL 
            AND AH.TRM_YR IS NOT NULL 
            AND ((TNote.TRNSCRPT_CD IS NOT NULL AND TNote.TRNSCRPT_CD <> ' ' )
                  OR (PS_Awd.PS_AWD_TY IS NOT NULL AND PS_Awd.PS_AWD_TY <> ' ')
                  OR (PS_Awd.PS_HNRS_CD IS NOT NULL AND PS_Awd.PS_HNRS_CD <> ' '))       
    GROUP BY AH.RowUpdatedOn ,
            AH.ISN_ST_ACDMC_HIST_A ,
            AH.STUDENT_ID ,
            AH.TRM_YR ,
            AH.CLASS_TY
Powered by BI Documenter