Description
(Criteria) Record source for Invoice report. Based on six tables. Includes expressions that concatenate first and last employee name and that use the CCur function to calculate extended price.
Fields
| Name | Type | Attributes | Description |
|---|---|---|---|
 ShipName |
nvarchar(40) | Nullable |
|
| ShipAddress |
nvarchar(60) | Nullable |
|
| ShipCity |
nvarchar(15) | Nullable |
|
| ShipRegion |
nvarchar(15) | Nullable |
|
| ShipPostalCode |
nvarchar(10) | Nullable |
|
| ShipCountry |
nvarchar(15) | Nullable |
|
| CustomerID |
nchar(5) | Nullable |
|
| CustomerName |
nvarchar(40) | ||
| Address |
nvarchar(60) | Nullable |
|
| City |
nvarchar(15) | Nullable |
|
| Region |
nvarchar(15) | Nullable |
|
| PostalCode |
nvarchar(10) | Nullable |
|
| Country |
nvarchar(15) | Nullable |
|
| Salesperson |
nvarchar(31) | ||
| OrderID |
int | ||
| OrderDate |
datetime | Nullable |
|
| RequiredDate |
datetime | Nullable |
|
| ShippedDate |
datetime | Nullable |
|
| ShipperName |
nvarchar(40) | ||
| ProductID |
int | ||
| ProductName |
nvarchar(40) | ||
| UnitPrice |
money | ||
| Quantity |
smallint | ||
| Discount |
real | ||
| ExtendedPrice |
money | Nullable |
|
| Freight |
money | Nullable |
Definition
create view Invoices AS
SELECT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Orders.CustomerID, Customers.CompanyName AS CustomerName, Customers.Address, Customers.City,
Customers.Region, Customers.PostalCode, Customers.Country,
(FirstName + ' ' + LastName) AS Salesperson,
Orders.OrderID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName As ShipperName,
"Order Details".ProductID, Products.ProductName, "Order Details".UnitPrice, "Order Details".Quantity,
"Order Details".Discount,
(CONVERT(money,("Order Details".UnitPrice*Quantity*(1-Discount)/100))*100) AS ExtendedPrice, Orders.Freight
FROM Shippers INNER JOIN
(Products INNER JOIN
(
(Employees INNER JOIN
(Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID)
ON Employees.EmployeeID = Orders.EmployeeID)
INNER JOIN "Order Details" ON Orders.OrderID = "Order Details".OrderID)
ON Products.ProductID = "Order Details".ProductID)
ON Shippers.ShipperID = Orders.ShipVia