Northwind Database (Document! X Sample)
Sales.vIndividualCustomer View
AdventureWorks Database > Sales Schema : Sales.vIndividualCustomer View
Description
Individual customers (names and addresses) that purchase Adventure Works Cycles products online.
Properties
Creation Date27/10/2017 14:33
Is Schema Bound
Encrypted
Ansi Nulls
Columns
 Column NameDescriptionDatatypeLengthAllow NullsDefaultFormula
  Integer4   
  VarWChar8  
  dbo.Name50   
  dbo.Name50  
  dbo.Name50   
  VarWChar10  
  dbo.Phone25  
  dbo.Name50  
  VarWChar50  
  Integer4   
  dbo.Name50   
  VarWChar60   
  VarWChar60  
  VarWChar30   
  dbo.Name50   
  VarWChar15   
  dbo.Name50   
  1410  
Objects that Sales.vIndividualCustomer depends on
 Database ObjectObject TypeDescriptionDep Level
Person.Address tablePerson.AddressTableStreet address information for customers, employees, and vendors.1
Person.AddressType tablePerson.AddressTypeTableTypes of addresses stored in the Address table. 1
Person.BusinessEntity tablePerson.BusinessEntityTableSource of the ID that connects vendors, customers, and employees with address and contact information.2
Person.BusinessEntityAddress tablePerson.BusinessEntityAddressTableCross-reference table mapping customers, vendors, and employees to their addresses.1
Person.CountryRegion tablePerson.CountryRegionTableLookup table containing the ISO standard codes for countries and regions.1
Sales.Customer tableSales.CustomerTableCurrent customer information. Also see the Person and Store tables.1
Person.EmailAddress tablePerson.EmailAddressTableWhere to send a person email.1
HumanResources.Employee tableHumanResources.EmployeeTableEmployee information such as salary, department, and title.4
dbo.NameStyle datatypedbo.NameStyleUser Defined Data Type 2
Person.Person tablePerson.PersonTableHuman beings involved with AdventureWorks: employees, customer contacts, and vendor contacts.1
Person.PersonPhone tablePerson.PersonPhoneTableTelephone number and type of a person.1
dbo.Phone datatypedbo.PhoneUser Defined Data Type 2
Person.PhoneNumberType tablePerson.PhoneNumberTypeTableType of phone number of a person.1
Sales.SalesPerson tableSales.SalesPersonTableSales representative current information.3
Sales.SalesTerritory tableSales.SalesTerritoryTableSales territory lookup table.2
Person.StateProvince tablePerson.StateProvinceTableState and province lookup table.1
Sales.Store tableSales.StoreTableCustomers (resellers) of Adventure Works products.2
dbo.ufnLeadingZeros functiondbo.ufnLeadingZerosUser Defined FunctionScalar function used by the Sales.Customer table to help set the account number.2
View Definition
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;
See Also

Related Objects

Sales Schema
AdventureWorks Database