USE SQL2008SBS
GO

CREATE VIEW Customers.CustomerOrders
AS
SELECT CASE WHEN a.CompanyName IS NOT NULL THEN a.CompanyName ELSE a.FirstName + ' ' + a.LastName END CustomerName,
b.AddressLine1, b.AddressLine2, b.AddressLine3, b.City, d.StateProvinceAbbrev, e.CountryName,
c.OrderDate, c.GrandTotal, c.FinalShipDate
FROM Customers.Customer a INNER JOIN Customers.CustomerAddress b ON a.CustomerID = b.CustomerID
INNER JOIN Orders.OrderHeader c ON a.CustomerID = c.CustomerID
INNER JOIN LookupTables.StateProvince d ON b.StateProvinceID = d.StateProvinceID
INNER JOIN LookupTables.Country e ON b.CountryID = e.CountryID
GO
SELECT CustomerName, AddressLine1, AddressLine2, AddressLine3, City, StateProvinceAbbrev, CountryName, OrderDate, GrandTotal, FinalShipDate
FROM Customers.CustomerOrders
GO