Website.Suppliers - WideWorldImporters

WideWorldImporters

- WideWorldImporters

Documentation	WideWorldImporters
Schema	Website
Name	Suppliers
Module	Website

Columns

		Name	Data type	Description
	1	SupplierID	int	Numeric ID used for reference to a supplier within the database
Numeric ID used for reference to a supplier within the database
	2	SupplierName	nvarchar(100)	Supplier's full name (usually a trading name)
Supplier's full name (usually a trading name)
	3	SupplierCategoryName	nvarchar(50)	Full name of the category that suppliers can be assigned to
Full name of the category that suppliers 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	WebsiteURL	nvarchar(256)	URL for the website for this supplier
URL for the website for this supplier
	9	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
	10	CityName	nvarchar(50)	Formal name of the city
Formal name of the city
	11	DeliveryLocation	geography	Geographic location for the supplier's office/warehouse
Geographic location for the supplier's office/warehouse
	12	SupplierReference	nvarchar(20)	Supplier reference for our organization (might be our account number at the supplier)
Supplier reference for our organization (might be our account number at the supplier)

Script

CREATE VIEW Website.Suppliers AS SELECT s.SupplierID, s.SupplierName, sc.SupplierCategoryName, pp.FullName AS PrimaryContact, ap.FullName AS AlternateContact, s.PhoneNumber, s.FaxNumber, s.WebsiteURL, dm.DeliveryMethodName AS DeliveryMethod, c.CityName AS CityName, s.DeliveryLocation AS DeliveryLocation, s.SupplierReference FROM Purchasing.Suppliers AS s LEFT OUTER JOIN Purchasing.SupplierCategories AS sc ON s.SupplierCategoryID = sc.SupplierCategoryID 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 [Application].DeliveryMethods AS dm ON s.DeliveryMethodID = dm.DeliveryMethodID LEFT OUTER JOIN [Application].Cities AS c ON s.DeliveryCityID = c.CityID

CREATE VIEW Website.Suppliers
AS
SELECT s.SupplierID,
       s.SupplierName,
       sc.SupplierCategoryName,
       pp.FullName AS PrimaryContact,
       ap.FullName AS AlternateContact,
       s.PhoneNumber,
       s.FaxNumber,
       s.WebsiteURL,
       dm.DeliveryMethodName AS DeliveryMethod,
       c.CityName AS CityName,
       s.DeliveryLocation AS DeliveryLocation,
       s.SupplierReference
FROM Purchasing.Suppliers AS s
LEFT OUTER JOIN Purchasing.SupplierCategories AS sc
ON s.SupplierCategoryID = sc.SupplierCategoryID
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 [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