AdventureWorks

Basic Index   Expanded Index

View Name:Sales.vIndividualCustomer
Description:Individual customers (names and addresses) that purchase Adventure Works Cycles products online.

FieldTypeNulls?
CustomerIDintNo
Titlenvarchar (8)Yes
FirstNamenvarchar (50)No
MiddleNamenvarchar (50)Yes
LastNamenvarchar (50)No
Suffixnvarchar (10)Yes
Phonenvarchar (25)Yes
EmailAddressnvarchar (50)Yes
EmailPromotionintNo
AddressTypenvarchar (50)No
AddressLine1nvarchar (60)No
AddressLine2nvarchar (60)Yes
Citynvarchar (30)No
StateProvinceNamenvarchar (50)No
PostalCodenvarchar (15)No
CountryRegionNamenvarchar (50)No
DemographicsxmlYes

CREATE VIEW [Sales].[vIndividualCustomer]
AS
SELECT
i.[CustomerID]
,c.[Title]
,c.[FirstName]
,c.[MiddleName]
,c.[LastName]
,c.[Suffix]
,c.[Phone]
,c.[EmailAddress]
,c.[EmailPromotion]
,at.[Name] AS [AddressType]
,a.[AddressLine1]
,a.[AddressLine2]
,a.[City]
,[StateProvinceName] = sp.[Name]
,a.[PostalCode]
,[CountryRegionName] = cr.[Name]
,i.[Demographics]
FROM [Sales].[Individual] i
INNER JOIN [Person].[Contact] c
ON c.[ContactID] = i.[ContactID]
INNER JOIN [Sales].[CustomerAddress] ca
ON ca.[CustomerID] = i.[CustomerID]
INNER JOIN [Person].[Address] a
ON a.[AddressID] = ca.[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 ca.[AddressTypeID] = at.[AddressTypeID]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]
FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'I');