Sales.vSalesPersonSalesByFiscalYears (view) - Revision history

Bot at 22:18, 23 June 2010

2010-06-23T22:18:03Z

Bot at 22:07, 2 February 2010

2010-02-02T22:07:38Z

Bot at 21:57, 28 December 2009

2009-12-28T21:57:14Z

Bot at 06:02, 30 November 2009

2009-11-30T06:02:07Z

Bot at 05:40, 30 November 2009

2009-11-30T05:40:21Z

Bot at 05:26, 30 November 2009

2009-11-30T05:26:46Z

Bot at 22:59, 26 November 2009

2009-11-26T22:59:38Z

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

 CREATE VIEW [Sales].[vSalesPe...

2009-01-03T12:36:44Z

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

New page

== wikibot ==

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

<pre>
CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]
AS
SELECT
pvt.[SalesPersonID]
,pvt.[FullName]
,pvt.[Title]
,pvt.[SalesTerritory]
,pvt.[2002]
,pvt.[2003]
,pvt.[2004]
FROM (SELECT
soh.[SalesPersonID]
,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
,e.[Title]
,st.[Name] AS [SalesTerritory]
,soh.[SubTotal]
,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[SalesPersonID] = soh.[SalesPersonID]
INNER JOIN [Sales].[SalesTerritory] st
ON sp.[TerritoryID] = st.[TerritoryID]
INNER JOIN [HumanResources].[Employee] e
ON soh.[SalesPersonID] = e.[EmployeeID]
INNER JOIN [Person].[Contact] c
ON e.[ContactID] = c.ContactID
) AS soh
PIVOT
(
SUM([SubTotal])
FOR [FiscalYear]
IN ([2002], [2003], [2004])
) AS pvt;
</pre>

@@ Line 2: / Line 2: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
-|-
+|- valign="top"
 | '''View
 | Sales.vSalesPersonSalesByFiscalYears
@@ Line 8: / Line 8: @@
 | '''Description
 | Uses PIVOT to return aggregated sales information for each sales representative.
 |}
 === Source ===
@@ Line 56: / Line 54: @@
 | '''Referenced Object
-|-
+|- valign="top"
 | Select
 | Table
 | [[HumanResources.Employee_(table)|HumanResources.Employee]]
-|-
+|- valign="top"
 | Select
 | Table
 | [[Person.Contact_(table)|Person.Contact]]
-|-
+|- valign="top"
 | Select
 | Table
 | [[Sales.SalesOrderHeader_(table)|Sales.SalesOrderHeader]]
-|-
+|- valign="top"
 | Select
 | Table
 | [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
-|-
+|- valign="top"
 | Select
 | Table
 | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
-|-
+|- valign="top"
 | Schema
 | Schema

@@ Line 3: / Line 3: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |-
-| '''view
+| '''View
 | Sales.vSalesPersonSalesByFiscalYears
 |- valign="top"
@@ Line 11: / Line 11: @@
 |}
 <pre>
 CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]
@@ Line 47: / Line 49: @@
 </pre>
+=== References ===
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
@@ Line 78: / Line 80: @@
 | Table
 | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
 |}

@@ Line 50: / Line 50: @@
 | Sales.vSalesPersonSalesByFiscalYears
 |- valign="top"
 | '''Description
 | Uses PIVOT to return aggregated sales information for each sales representative.
 |-
 |}

@@ Line 97: / Line 97: @@
 {| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 |- style="background:silver"
 | '''Referenced Object
 |-
 | [[HumanResources.Employee_(table)|HumanResources.Employee]]
 | Table
 | Select
 |-
-| [[Person.Contact_(table)|Person.Contact]]
+| Select
 | Table
-| Select
+| [[Sales.SalesPerson_(table)|Sales.SalesPerson]]
 |-
 | Select
 | Table
 | [[Sales.SalesTerritory_(table)|Sales.SalesTerritory]]
-| Table
 |-
 | Schema
 | Schema
 |}

@@ Line 43: / Line 43: @@
 ) AS pvt;
 </pre>
 == automatically generated ==
@@ Line 50: / Line 49: @@
 | '''view
 | Sales.vSalesPersonSalesByFiscalYears
-|-
+|- valign="top"
 | '''Description
@@ Line 101: / Line 100: @@
 | '''Object Type
 | '''Dependency Type
-−
 |-
 | [[HumanResources.Employee_(table)|HumanResources.Employee]]
@@ Line 122: / Line 121: @@
 | Table
 | Select
 |}

← Older revision		Revision as of 22:59, 26 November 2009
Line 43:		Line 43:
	) AS pvt;		) AS pvt;
	</pre>		</pre>
		+
		+	== automatically generated ==
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|-
		+	\| '''view
		+	\| Sales.vSalesPersonSalesByFiscalYears
		+	\|-
		+
		+	\| '''Description
		+	\| Uses PIVOT to return aggregated sales information for each sales representative.
		+	\|-
		+
		+	\|}
		+
		+	<pre>
		+
		+	CREATE VIEW [Sales].[vSalesPersonSalesByFiscalYears]
		+	AS
		+	SELECT
		+	pvt.[SalesPersonID]
		+	,pvt.[FullName]
		+	,pvt.[Title]
		+	,pvt.[SalesTerritory]
		+	,pvt.[2002]
		+	,pvt.[2003]
		+	,pvt.[2004]
		+	FROM (SELECT
		+	soh.[SalesPersonID]
		+	,c.[FirstName] + ' ' + COALESCE(c.[MiddleName], '') + ' ' + c.[LastName] AS [FullName]
		+	,e.[Title]
		+	,st.[Name] AS [SalesTerritory]
		+	,soh.[SubTotal]
		+	,YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
		+	FROM [Sales].[SalesPerson] sp
		+	INNER JOIN [Sales].[SalesOrderHeader] soh
		+	ON sp.[SalesPersonID] = soh.[SalesPersonID]
		+	INNER JOIN [Sales].[SalesTerritory] st
		+	ON sp.[TerritoryID] = st.[TerritoryID]
		+	INNER JOIN [HumanResources].[Employee] e
		+	ON soh.[SalesPersonID] = e.[EmployeeID]
		+	INNER JOIN [Person].[Contact] c
		+	ON e.[ContactID] = c.ContactID
		+	) AS soh
		+	PIVOT
		+	(
		+	SUM([SubTotal])
		+	FOR [FiscalYear]
		+	IN ([2002], [2003], [2004])
		+	) AS pvt;
		+	</pre>
		+
		+
		+	{\| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
		+	\|- style="background:silver"
		+	\| '''Referenced Object
		+	\| '''Object Type
		+	\| '''Dependency Type
		+
		+	\|-
		+	\| [[HumanResources.Employee_(table)\|HumanResources.Employee]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Person.Contact_(table)\|Person.Contact]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.SalesOrderHeader_(table)\|Sales.SalesOrderHeader]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.SalesPerson_(table)\|Sales.SalesPerson]]
		+	\| Table
		+	\| Select
		+	\|-
		+	\| [[Sales.SalesTerritory_(table)\|Sales.SalesTerritory]]
		+	\| Table
		+	\| Select
		+	\|}