View: Sales.vStoreWithDemographics

Properties

Owner:	Sales
Schema bound:
Encrypted:
Creation Date:	04/26/2006
Modification Date:	04/26/2006
Description:	Stores (names and addresses) that sell Adventure Works Cycles products to consumers.

Columns

Name	Data Type	Length	NULL	IsGUID	Description
CustomerID	int	4
Name	Name	100
ContactType	Name	100
Title	nvarchar	8
FirstName	Name	100
MiddleName	Name	100
LastName	Name	100
Suffix	nvarchar	10
Phone	Phone	50
EmailAddress	nvarchar	50
EmailPromotion	int	4
AddressType	Name	100
AddressLine1	nvarchar	60
AddressLine2	nvarchar	60
City	nvarchar	30
StateProvinceName	Name	100
PostalCode	nvarchar	15
CountryRegionName	Name	100
AnnualSales	money	8
AnnualRevenue	money	8
BankName	nvarchar	50
BusinessType	nvarchar	5
YearOpened	int	4
Specialty	nvarchar	50
SquareFeet	int	4
Brands	nvarchar	30
Internet	nvarchar	30
NumberEmployees	int	4

Total: 28 column(s)

Objects that [Sales].[vStoreWithDemographics] depends on

Object Name	Owner	Object Type	Dep Level
Flag	dbo	User Defined type	1
Name	dbo	User Defined type	1
NameStyle	dbo	User Defined type	1
Phone	dbo	User Defined type	1
ufnLeadingZeros	dbo	Function	2
AddressType	Person	Table	2
Contact	Person	Table	2
ContactType	Person	Table	2
CountryRegion	Person	Table	2
SalesTerritory	Sales	Table	2
Customer	Sales	Table	3
Employee	HumanResources	Table	3
StateProvince	Person	Table	3
Address	Person	Table	4
SalesPerson	Sales	Table	4
CustomerAddress	Sales	Table	5
Store	Sales	Table	5
StoreContact	Sales	Table	6

Total: 18 objects

SQL

CREATE VIEW [Sales].[vStoreWithDemographics] AS
SELECT
    s.[CustomerID]
    ,s.[Name]
    ,ct.[Name] AS [ContactType]
    ,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]
    ,sp.[Name] AS [StateProvinceName]
    ,a.[PostalCode]
    ,cr.[Name] AS [CountryRegionName]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/AnnualSales)[1]', 'money') AS [AnnualSales]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/AnnualRevenue)[1]', 'money') AS [AnnualRevenue]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/BankName)[1]', 'nvarchar(50)') AS [BankName]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/BusinessType)[1]', 'nvarchar(5)') AS [BusinessType]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/Specialty)[1]', 'nvarchar(50)') AS [Specialty]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/SquareFeet)[1]', 'integer') AS [SquareFeet]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/Brands)[1]', 'nvarchar(30)') AS [Brands]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/Internet)[1]', 'nvarchar(30)') AS [Internet]
    ,s.[Demographics].value('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/StoreSurvey/NumberEmployees)[1]', 'integer') AS [NumberEmployees]
FROM [Sales].[Store] s
    INNER JOIN [Sales].[StoreContact] sc
    ON sc.[CustomerID] = s.[CustomerID]
    INNER JOIN [Person].[Contact] c
    ON c.[ContactID] = sc.[ContactID]
    INNER JOIN [Person].[ContactType] ct
    ON sc.[ContactTypeID] = ct.[ContactTypeID]
    INNER JOIN [Sales].[CustomerAddress] ca
    ON ca.[CustomerID] = s.[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 s.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID]
    FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');