Sales.vStoreWithDemographics (view) - Revision history

Bot at 22:18, 23 June 2010

2010-06-23T22:18:04Z

Bot at 22:07, 2 February 2010

2010-02-02T22:07:26Z

Bot at 21:57, 28 December 2009

2009-12-28T21:57:17Z

Bot at 06:01, 30 November 2009

2009-11-30T06:01:57Z

Bot at 05:40, 30 November 2009

2009-11-30T05:40:10Z

Bot at 05:26, 30 November 2009

2009-11-30T05:26:35Z

Bot at 22:59, 26 November 2009

2009-11-26T22:59:43Z

Bot: New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Sales.vStoreWithDemographics |- |}

 CREATE VIEW [Sales].[vStoreWithDemogr...

2009-01-03T12:36:47Z

New page: == wikibot == {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse" |- | '''view | Sales.vStoreWithDemographics |- |} <pre> CREATE VIEW [Sales].[vStoreWithDemogr...

New page

== wikibot ==

{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
| '''view
| Sales.vStoreWithDemographics
|-
|}

<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>

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''View
 | Sales.vStoreWithDemographics
@@ Line 8: / Line 8: @@
 | '''Description
 | Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
 |}
 === Source ===
@@ Line 82: / Line 80: @@
 | '''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

@@ Line 3: / Line 3: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |-
-| '''view
+| '''View
 | Sales.vStoreWithDemographics
 |- valign="top"
@@ Line 11: / Line 11: @@
 |}
 <pre>
 CREATE VIEW [Sales].[vStoreWithDemographics] AS
@@ Line 73: / Line 75: @@
 </pre>
+=== References ===
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
@@ Line 129: / Line 131: @@
 | Table
 | [[Sales.StoreContact_(table)|Sales.StoreContact]]
 |}

@@ Line 76: / Line 76: @@
 | Sales.vStoreWithDemographics
 |- valign="top"
 | '''Description
 | Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
 |-
 |}

@@ Line 149: / Line 149: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
 | '''Referenced Object
 |-
 | [[Person.Address_(table)|Person.Address]]
 | Table
 | Select
 |-
-| [[Person.AddressType_(table)|Person.AddressType]]
+| Select
 | Table
 | Select
 |-
-| [[Person.Contact_(table)|Person.Contact]]
+| Select
 | Table
 | Select
 |-
 | Select
 | Table
-| Select
+| [[Sales.CustomerAddress_(table)|Sales.CustomerAddress]]
 |-
 | Select
 | Table
-| Select
+| [[Sales.Store_(table)|Sales.Store]]
 |-
 | Select
 | Table
 | [[Sales.StoreContact_(table)|Sales.StoreContact]]
-| Table
 |-
 | Schema
 | Schema
 |}

@@ Line 69: / Line 69: @@
      FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
 </pre>
 == automatically generated ==
@@ Line 76: / Line 75: @@
 | '''view
 | Sales.vStoreWithDemographics
-|-
+|- valign="top"
 | '''Description
@@ Line 153: / Line 152: @@
 | '''Object Type
 | '''Dependency Type
-−
 |-
 | [[Person.Address_(table)|Person.Address]]
@@ Line 194: / Line 193: @@
 | Table
 | Select
 |}

← Older revision		Revision as of 22:59, 26 November 2009
Line 69:		Line 69:
	FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');		FROM [Sales].[Customer] WHERE UPPER([Sales].[Customer].[CustomerType]) = 'S');
	</pre>		</pre>
		+
		+	== automatically generated ==
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|-
		+	\| '''view
		+	\| Sales.vStoreWithDemographics
		+	\|-
		+
		+	\| '''Description
		+	\| Stores (names and addresses) that sell Adventure Works Cycles products to consumers.
		+	\|-
		+
		+	\|}
		+
		+	<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>
		+
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|- style="background:silver"
		+	\| '''Referenced Object
		+	\| '''Object Type
		+	\| '''Dependency Type
		+
		+	\|-
		+	\| [[Person.Address_(table)\|Person.Address]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.AddressType_(table)\|Person.AddressType]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.Contact_(table)\|Person.Contact]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.ContactType_(table)\|Person.ContactType]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.CountryRegion_(table)\|Person.CountryRegion]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.StateProvince_(table)\|Person.StateProvince]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.Customer_(table)\|Sales.Customer]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.CustomerAddress_(table)\|Sales.CustomerAddress]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.Store_(table)\|Sales.Store]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.StoreContact_(table)\|Sales.StoreContact]]
		+	\| Table
		+	\| Select
		+	\|}