DWStaging
 dbo.arch_StudentAddressesAll (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_StudentAddressesAll
  Properties
Property Value
Name arch_StudentAddressesAll
Schema dbo
Quoted Identifier Status True
Ansi Nulls Status True
Returns View Metadata False
Create Date 10/1/2014 9:51 AM
Description
  Columns
Column Name
Data Type
Allow Nulls
Fulltext Indexed
Identity
Description / Computed Formula
varchar(2)
True
False
False
varchar(2)
True
False
False
varchar(20)
True
False
False
varchar(3)
True
False
False
varchar(2)
True
False
False
varchar(32)
True
False
False
varchar(32)
True
False
False
varchar(11)
True
False
False
varchar(20)
True
False
False
varchar(3)
True
False
False
varchar(2)
True
False
False
varchar(20)
True
False
False
varchar(3)
True
False
False
varchar(2)
True
False
False
varchar(32)
True
False
False
varchar(32)
True
False
False
varchar(11)
True
False
False
datetime2(27, 7)
True
False
False
varchar(2)
True
False
False
varchar(20)
True
False
False
varchar(3)
True
False
False
varchar(2)
True
False
False
varchar(32)
True
False
False
varchar(32)
True
False
False
varchar(11)
True
False
False
varchar(2)
True
False
False
varchar(20)
True
False
False
varchar(3)
True
False
False
varchar(2)
True
False
False
varchar(32)
True
False
False
varchar(32)
True
False
False
varchar(11)
True
False
False
varchar(2)
True
False
False
varchar(32)
True
False
False
varchar(32)
True
False
False
varchar(9)
True
False
False
varchar(11)
True
False
False
  Triggers
Name Insert Update Delete Instead
  Indexes
Name Clustered Columns
  Parent Dependencies (objects that dbo.arch_StudentAddressesAll depends on)
Name Type
Table
Table
View
  Child Dependencies (objects that depend on dbo.arch_StudentAddressesAll)
Name Type
VIEW
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: View [dbo].[arch_StudentAddressesAll] Script Date: 03/09/2017 16:47:09 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[arch_StudentAddressesAll]
AS
SELECT        s.STUDENT_ID AS StudentId,
                             (SELECT        MAX(ADDR_TY) AS expr1
                               FROM            ST_ADDRESSES_A
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS AddressType,
                             (SELECT        MAX(STREET_1) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_35
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS Street1,
                             (SELECT        MAX(STREET_2) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_34
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS Street2,
                             (SELECT        MAX(CITY) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_33
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS City,
                             (SELECT        MAX(STATE) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_32
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS State,
                             (SELECT        MAX(ZIP_CD) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_31
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS ZipCode,
                             (SELECT        MAX(CNTRY) AS expr3
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_30
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '01')) AS Country,
                             (SELECT        MAX(ADDR_TY) AS expr1
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_29
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentAddressType,
                             (SELECT        MAX(STREET_1) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_28
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentStreet1,
                             (SELECT        MAX(STREET_2) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_27
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentStreet2,
                             (SELECT        MAX(CITY) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_26
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentCity,
                             (SELECT        MAX(STATE) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_25
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentState,
                             (SELECT        MAX(ZIP_CD) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_24
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentZipCode,
                             (SELECT        MAX(CNTRY) AS expr3
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_23
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '02')) AS PermanentCountry,
                             (SELECT        MAX(ADDR_TY) AS expr1
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_22
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyAddressType,
                             (SELECT        MAX(STREET_1) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_21
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyStreet1,
                             (SELECT        MAX(STREET_2) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_20
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyStreet2,
                             (SELECT        MAX(CITY) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_19
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyCity,
                             (SELECT        MAX(STATE) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_18
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyState,
                             (SELECT        MAX(ZIP_CD) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_17
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyZipCode,
                             (SELECT        MAX(CNTRY) AS expr3
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_16
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '03')) AS EmergencyCountry,
                             (SELECT        MAX(ADDR_TY) AS expr1
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_15
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateAddressType,
                             (SELECT        MAX(STREET_1) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_14
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateStreet1,
                             (SELECT        MAX(STREET_2) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_13
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateStreet2,
                             (SELECT        MAX(CITY) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_12
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateCity,
                             (SELECT        MAX(STATE) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_11
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateState,
                             (SELECT        MAX(ZIP_CD) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_10
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateZipCode,
                             (SELECT        MAX(CNTRY) AS expr3
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_9
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '04')) AS AlternateCountry,
                             (SELECT        MAX(ADDR_TY) AS expr1
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_8
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyAddressType,
                             (SELECT        MAX(STREET_1) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_7
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyStreet1,
                             (SELECT        MAX(STREET_2) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_6
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyStreet2,
                             (SELECT        MAX(CITY) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_5
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyCity,
                             (SELECT        MAX(STATE) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_4
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyState,
                             (SELECT        MAX(ZIP_CD) AS expr2
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_3
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyZipCode,
                             (SELECT        MAX(CNTRY) AS expr3
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_2
                               WHERE        (STUDENT_ID = s.STUDENT_ID) AND (ADDR_TY = '05')) AS ResidencyCountry,
                             (SELECT        MAX(RowUpdatedOn) AS expr3
                               FROM            ST_ADDRESSES_A AS ST_ADDRESSES_A_1
                               WHERE        (STUDENT_ID = s.STUDENT_ID)) AS LastUpdate
FROM            ST_ADDRESSES_A AS a RIGHT OUTER JOIN
                         ST_STDNT_A s ON s.STUDENT_ID = s.STUDENT_ID
GROUP BY s.STUDENT_ID
Powered by BI Documenter