Difference between revisions of "Sales.vStoreWithDemographics (view)"

From dbscript Online Help
Jump to: navigation, search
(New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Sales.vStoreWithDemographics |- |} <pre> CREATE VIEW [Sales].[vStoreWithDemogr...)
 
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
== wikibot ==
 
== wikibot ==
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- valign="top"
 +
| '''View
 +
| Sales.vStoreWithDemographics
 +
|- valign="top"
 +
| '''Description
 +
| Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
 +
|}
 +
 +
=== Source ===
 +
<pre>
 +
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');
 +
</pre>
 +
 +
=== References ===
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.Address_(table)|Person.Address]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.AddressType_(table)|Person.AddressType]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.ContactType_(table)|Person.ContactType]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.Customer_(table)|Sales.Customer]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
 +
|- valign="top"
 +
| Select
 +
| Table
 +
| [[Sales.StoreContact_(table)|Sales.StoreContact]]
 +
 +
|- valign="top"
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
 +
 +
|}
 +
 +
 +
== automatically generated ==
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
Line 5: Line 144:
 
| '''view
 
| '''view
 
| Sales.vStoreWithDemographics
 
| Sales.vStoreWithDemographics
 +
|- valign="top"
 +
| '''Description
 +
| Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
 
|-
 
|-
 
|}
 
|}
  
 
<pre>
 
<pre>
 +
 
CREATE VIEW [Sales].[vStoreWithDemographics] AS  
 
CREATE VIEW [Sales].[vStoreWithDemographics] AS  
 
SELECT  
 
SELECT  
Line 69: Line 212:
 
     FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
 
     FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
 
</pre>
 
</pre>
 +
 +
 +
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 +
|- style="background:silver"
 +
| '''Dependency Type
 +
| '''Object Type
 +
| '''Referenced Object
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.Address_(table)|Person.Address]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.AddressType_(table)|Person.AddressType]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.Contact_(table)|Person.Contact]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.ContactType_(table)|Person.ContactType]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.CountryRegion_(table)|Person.CountryRegion]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Person.StateProvince_(table)|Person.StateProvince]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.Customer_(table)|Sales.Customer]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.Store_(table)|Sales.Store]]
 +
 +
|-
 +
| Select
 +
| Table
 +
| [[Sales.StoreContact_(table)|Sales.StoreContact]]
 +
 +
|-
 +
| Schema
 +
| Schema
 +
| [[Sales_(schema)|Sales]]
 +
 +
|}

Latest revision as of 23:18, 23 June 2010

wikibot[edit]

View Sales.vStoreWithDemographics
Description Stores (names and addresses) that sell Adventure Works Cycles products to consumers.

Source[edit]

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

References[edit]

Dependency Type Object Type Referenced Object
Select Table Person.Address
Select Table Person.AddressType
Select Table Person.Contact
Select Table Person.ContactType
Select Table Person.CountryRegion
Select Table Person.StateProvince
Select Table Sales.Customer
Select Table Sales.CustomerAddress
Select Table Sales.Store
Select Table Sales.StoreContact
Schema Schema Sales


automatically generated[edit]

view Sales.vStoreWithDemographics
Description Stores (names and addresses) that sell Adventure Works Cycles products to consumers. 

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


Dependency Type Object Type Referenced Object
Select Table Person.Address
Select Table Person.AddressType
Select Table Person.Contact
Select Table Person.ContactType
Select Table Person.CountryRegion
Select Table Person.StateProvince
Select Table Sales.Customer
Select Table Sales.CustomerAddress
Select Table Sales.Store
Select Table Sales.StoreContact
Schema Schema Sales
Retrieved from "http://dbshelp.devio.at/index.php?title=Sales.vStoreWithDemographics_(view)&oldid=2687"