DWStaging
 dbo.arch_StudentLastTermAttended (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_StudentLastTermAttended
  Properties
Property Value
Name arch_StudentLastTermAttended
Schema dbo
Quoted Identifier Status True
Ansi Nulls Status True
Returns View Metadata False
Create Date 10/1/2014 9:02 AM
Description
  Columns
Column Name
Data Type
Allow Nulls
Fulltext Indexed
Identity
Description / Computed Formula
varchar(6)
True
False
False
varchar(9)
True
False
False
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
  Parent Dependencies (objects that dbo.arch_StudentLastTermAttended depends on)
Name Type
Table
Table
View
  Child Dependencies (objects that depend on dbo.arch_StudentLastTermAttended)
Name Type
VIEW
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: View [dbo].[arch_StudentLastTermAttended] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_StudentLastTermAttended]
AS
SELECT     c.STDNT_ID AS Student_id, MAX(c.EFF_TRM) AS LastTermAttended
FROM         dbo.ST_STDNT_CLS_A AS c 
INNER JOIN
(
SELECT SUBSTRING([SESSION_KEY],1,6) AS CurrentTerm
      FROM ST_SESSION_A
      WHERE SESSION_KEY IS NOT NULL
      AND SUBSTRING(SESSION_KEY,7,4) = '1 '
            AND convert(varchar(8), getdate(), 112) BETWEEN SESS_BEG_DT AND SESS_END_DT
) Cterm
       ON c.EFF_TRM < Cterm.CurrentTerm
WHERE     (c.TRNSCTN_TY = 'A' OR
                      c.TRNSCTN_TY = 'W') AND (c.CRS_ID <> 'orn0001') AND (c.CRS_ID <> 'gro9999')
   AND (STDNT_ID IS NOT NULL) AND (EFF_TRM IS NOT NULL) AND (CRS_ID IS NOT NULL) AND (REF_NUM IS NOT NULL)                      
GROUP BY c.STDNT_ID
Powered by BI Documenter