 | | | | CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
|
| | | | |
| | | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | Quantity | | | dbo.Customers | | | | user table | | | | no | | | | no | | | | CustomerID | | | dbo.Products | | | | user table | | | | no | | | | no | | | | ProductName | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | OrderID | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | ProductID | | | dbo.Orders | | | | user table | | | | no | | | | no | | | | OrderID | | | dbo.Orders | | | | user table | | | | no | | | | no | | | | CustomerID | | | dbo.Products | | | | user table | | | | no | | | | no | | | | ProductID | |
| | | | |
| | | | CREATE PROCEDURE SalesByCategory
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
|
| | | | |
| | | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | Discount | | | dbo.Categories | | | | user table | | | | no | | | | no | | | | CategoryID | | | dbo.Categories | | | | user table | | | | no | | | | no | | | | CategoryName | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | ProductID | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | UnitPrice | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | Quantity | | | dbo.Products | | | | user table | | | | no | | | | no | | | | ProductName | | | dbo.Products | | | | user table | | | | no | | | | no | | | | CategoryID | | | dbo.Order Details | | | | user table | | | | no | | | | no | | | | OrderID | | | dbo.Orders | | | | user table | | | | no | | | | no | | | | OrderID | | | dbo.Orders | | | | user table | | | | no | | | | no | | | | OrderDate | | | dbo.Products | | | | user table | | | | no | | | | no | | | | ProductID | |
| | | | |
|
|
