Welcome Guest, you are in: Login

devio dbscript ScrewTurn Wiki

RSS RSS

Navigation





Search the wiki
»

PoweredBy

wikibot

ViewSales.vIndividualCustomer
DescriptionIndividual customers (names and addresses) that purchase Adventure Works Cycles products online.

Source

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

References

Dependency TypeObject TypeReferenced Object
SelectTablePerson.Address
SelectTablePerson.AddressType
SelectTablePerson.Contact
SelectTablePerson.CountryRegion
SelectTablePerson.StateProvince
SelectTableSales.Customer
SelectTableSales.CustomerAddress
SelectTableSales.Individual
SchemaSchemaSales

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam.