View: [AdventureWorks2016CTP3].[Sales].[vIndividualCustomer]

CollapseAll image

Collapse image View properties


Name  Value 
 Schema   [Sales] 
 Owner   [dbo] 
 Creation date   16.11.2015 
 Is schema bound   
 Encrypted   
 ID   1085246921 

Collapse image Creation options


Name Value
QUOTED_IDENTIFIER ON
ANSI_NULLS ON

Collapse image Resultset


Name  Description Data type Max length
BusinessEntityID   int 4
Title   nvarchar 8
FirstName   [dbo].[Name] 50
MiddleName   [dbo].[Name] 50
LastName   [dbo].[Name] 50
Suffix   nvarchar 10
PhoneNumber   [dbo].[Phone] 25
PhoneNumberType   [dbo].[Name] 50
EmailAddress   nvarchar 50
EmailPromotion   int 4
AddressType   [dbo].[Name] 50
AddressLine1   nvarchar 60
AddressLine2   nvarchar 60
City   nvarchar 30
StateProvinceName   [dbo].[Name] 50
PostalCode   nvarchar 15
CountryRegionName   [dbo].[Name] 50
Demographics   xml 2147483647
Total: 18 column(s)

Collapse image Objects that [Sales].[vIndividualCustomer] depends on


Object name Object type Dep level
[Sales] Schema 1
[Person].[Address] Table 1
[Person].[AddressType] Table 1
[Person].[BusinessEntityAddress] Table 1
[Person].[CountryRegion] Table 1
[Sales].[Customer] Table 1
[Person].[EmailAddress] Table 1
[Person].[Person] Table 1
[Person].[PersonPhone] Table 1
[Person].[PhoneNumberType] Table 1
[Person].[StateProvince] Table 1
Total 11 object(s)

Collapse image Extended properties


Name  Value 
MS_Description Individual customers (names and addresses) that purchase Adventure Works Cycles products online.

Collapse image SQL


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [Sales].[vIndividualCustomer] 
AS 
SELECT 
    p.[BusinessEntityID]
    ,p.[Title]
    ,p.[FirstName]
    ,p.[MiddleName]
    ,p.[LastName]
    ,p.[Suffix]
    ,pp.[PhoneNumber]
    ,pnt.[Name] AS [PhoneNumberType]
    ,ea.[EmailAddress]
    ,p.[EmailPromotion]
    ,at.[Name] AS [AddressType]
    ,a.[AddressLine1]
    ,a.[AddressLine2]
    ,a.[City]
    ,[StateProvinceName] = sp.[Name]
    ,a.[PostalCode]
    ,[CountryRegionName] = cr.[Name]
    ,p.[Demographics]
FROM [Person].[Person] p
    INNER JOIN [Person].[BusinessEntityAddress] bea 
    ON bea.[BusinessEntityID] = p.[BusinessEntityID] 
    INNER JOIN [Person].[Address] a 
    ON a.[AddressID] = bea.[AddressID]
    INNER JOIN [Person].[StateProvince] sp 
    ON sp.[StateProvinceID] = a.[StateProvinceID]
    INNER JOIN [Person].[CountryRegion] cr 
    ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
    INNER JOIN [Person].[AddressType] at 
    ON at.[AddressTypeID] = bea.[AddressTypeID]
    INNER JOIN [Sales].[Customer] c
    ON c.[PersonID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[EmailAddress] ea
    ON ea.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PersonPhone] pp
    ON pp.[BusinessEntityID] = p.[BusinessEntityID]
    LEFT OUTER JOIN [Person].[PhoneNumberType] pnt
    ON pnt.[PhoneNumberTypeID] = pp.[PhoneNumberTypeID]
WHERE c.StoreID IS NULL;
GO
EXEC sp_addextendedproperty N'MS_Description', N'Individual customers (names and addresses) that purchase Adventure Works Cycles products online.', 'SCHEMA', N'Sales', 'VIEW', N'vIndividualCustomer', NULL, NULL
GO

Collapse image See also


List of views