Sales.vIndividualCustomer View

Description

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

Properties

Creation Date	08/01/2010 08:41
Is Schema Bound
Encrypted
Ansi Nulls

Columns

Column Name	Datatype	Length
CustomerID	int	4
Title	nvarchar	8
FirstName	dbo.Name	50
MiddleName	dbo.Name	50
LastName	dbo.Name	50
Suffix	nvarchar	10
Phone	dbo.Phone	25
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	Sales.IndividualSurveySchemaCollection	16

Objects that Sales.vIndividualCustomer depends on

Database Object	Object Type	Description	Dep Level
Person.Address	Table	Street address information for customers, employees, and vendors.	1
Person.AddressType	Table	Types of addresses stored in the Address table.	1
Person.Contact	Table	Names of each employee, customer contact, and vendor contact.	1
Person.CountryRegion	Table	Lookup table containing the ISO standard codes for countries and regions.	1
Sales.Customer	Table	Current customer information. Also see the Individual and Store tables.	1
Sales.CustomerAddress	Table	Cross-reference table mapping customers to their address(es).	1
HumanResources.Employee	Table	Employee information such as salary, department, and title.	4
dbo.ErrorLog	Table	Audit table tracking errors in the the AdventureWorks database that are caught by the CATCH block of a TRY...CATCH construct. Data is inserted by stored procedure dbo.uspLogError when it is executed from inside the CATCH block of a TRY...CATCH construct.	4
dbo.Flag	User Defined Data Type		2
Sales.Individual	Table	Demographic data about customers that purchase Adventure Works products online.	1
dbo.NameStyle	User Defined Data Type		2
dbo.Phone	User Defined Data Type		2
Sales.SalesPerson	Table	Sales representative current information.	3
Sales.SalesTerritory	Table	Sales territory lookup table.	2
Person.StateProvince	Table	State and province lookup table.	1
Sales.Store	Table	Customers (resellers) of Adventure Works products.	2
dbo.ufnLeadingZeros	User Defined Function	Scalar function used by the Sales.Customer table to help set the account number.	2
dbo.uspLogError	Stored Procedure	Logs error information in the ErrorLog table about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without inserting error information.	3
dbo.uspPrintError	Stored Procedure	Prints error information about the error that caused execution to jump to the CATCH block of a TRY...CATCH construct. Should be executed from within the scope of a CATCH block otherwise it will return without printing any error information.	3

View Definition

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');

Related Objects

Sales Schema
AdventureWorks Database