Website.Customers - WideWorldImporters

WideWorldImporters

- WideWorldImporters

Documentation	WideWorldImporters
Schema	Website
Name	Customers
Module	Website

Columns

		Name	Data type	Description
	1	CustomerID	int	Numeric ID used for reference to a customer within the database
Numeric ID used for reference to a customer within the database
	2	CustomerName	nvarchar(100)	Customer's full name (usually a trading name)
Customer's full name (usually a trading name)
	3	CustomerCategoryName	nvarchar(50)	Full name of the category that customers can be assigned to
Full name of the category that customers can be assigned to
	4	PrimaryContact	nvarchar(50)	Full name for person.
Full name for person.
	5	AlternateContact	nvarchar(50)	Full name for person.
Full name for person.
	6	PhoneNumber	nvarchar(20)	Phone number
Phone number
	7	FaxNumber	nvarchar(20)	Fax number
Fax number
	8	BuyingGroupName	nvarchar(50)	Full name of a buying group that customers can be members of
Full name of a buying group that customers can be members of
	9	WebsiteURL	nvarchar(256)	URL for the website for this customer
URL for the website for this customer
	10	DeliveryMethod	nvarchar(50)	Full name of methods that can be used for delivery of customer orders
Full name of methods that can be used for delivery of customer orders
	11	CityName	nvarchar(50)	Formal name of the city
Formal name of the city
	12	DeliveryLocation	geography	Geographic location for the customer's office/warehouse
Geographic location for the customer's office/warehouse
	13	DeliveryRun	nvarchar(5)	Normal delivery run for this customer
Normal delivery run for this customer
	14	RunPosition	nvarchar(5)	Normal position in the delivery run for this customer
Normal position in the delivery run for this customer

Script

CREATE VIEW Website.Customers AS SELECT s.CustomerID, s.CustomerName, sc.CustomerCategoryName, pp.FullName AS PrimaryContact, ap.FullName AS AlternateContact, s.PhoneNumber, s.FaxNumber, bg.BuyingGroupName, s.WebsiteURL, dm.DeliveryMethodName AS DeliveryMethod, c.CityName AS CityName, s.DeliveryLocation AS DeliveryLocation, s.DeliveryRun, s.RunPosition FROM Sales.Customers AS s LEFT OUTER JOIN Sales.CustomerCategories AS sc ON s.CustomerCategoryID = sc.CustomerCategoryID LEFT OUTER JOIN [Application].People AS pp ON s.PrimaryContactPersonID = pp.PersonID LEFT OUTER JOIN [Application].People AS ap ON s.AlternateContactPersonID = ap.PersonID LEFT OUTER JOIN Sales.BuyingGroups AS bg ON s.BuyingGroupID = bg.BuyingGroupID LEFT OUTER JOIN [Application].DeliveryMethods AS dm ON s.DeliveryMethodID = dm.DeliveryMethodID LEFT OUTER JOIN [Application].Cities AS c ON s.DeliveryCityID = c.CityID

CREATE VIEW Website.Customers
AS
SELECT s.CustomerID,
       s.CustomerName,
       sc.CustomerCategoryName,
       pp.FullName AS PrimaryContact,
       ap.FullName AS AlternateContact,
       s.PhoneNumber,
       s.FaxNumber,
       bg.BuyingGroupName,
       s.WebsiteURL,
       dm.DeliveryMethodName AS DeliveryMethod,
       c.CityName AS CityName,
       s.DeliveryLocation AS DeliveryLocation,
       s.DeliveryRun,
       s.RunPosition
FROM Sales.Customers AS s
LEFT OUTER JOIN Sales.CustomerCategories AS sc
ON s.CustomerCategoryID = sc.CustomerCategoryID
LEFT OUTER JOIN [Application].People AS pp
ON s.PrimaryContactPersonID = pp.PersonID
LEFT OUTER JOIN [Application].People AS ap
ON s.AlternateContactPersonID = ap.PersonID
LEFT OUTER JOIN Sales.BuyingGroups AS bg
ON s.BuyingGroupID = bg.BuyingGroupID
LEFT OUTER JOIN [Application].DeliveryMethods AS dm
ON s.DeliveryMethodID = dm.DeliveryMethodID
LEFT OUTER JOIN [Application].Cities AS c
ON s.DeliveryCityID = c.CityID

Exported: 2019-02-04 23:04, Last imported: 2018-08-10 09:51