Proc_problem
Sep 30, 2006
Hi... when i run this procedure
ALTER PROCEDURE dbo.StoredProcedure3
AS
select Title, Quantity, OrderDate
from Orders, OrderDetails, Product
where
OrderDate > '2006-09-01' and OrderDate < '2006-10-01'
and
Orders.OrderID = OrderDetails.OrderID
and
Product.productID = OrderDetails.productID
order by title
I get this result
Title Quantity OrderDate
-------------------------------------------------- ----------- -----------------------
Charlie & the Chocolate Factory 1 26/09/2006 4:05:41 PM
Charlie & the Chocolate Factory 1 26/09/2006 6:41:44 PM
Charlie & the Chocolate Factory 1 26/09/2006 8:24:17 PM
Cinderalla Man 1 26/09/2006 6:24:11 PM
Cinderalla Man 1 28/09/2006 4:13:12 PM
** What do i have to do to add up the Total of the titles so i get this result instead??
Charlie & the Chocolate Factory 3 (OrderDate wont be included)
Cinderalla Man 2
Cheers!!!
View 2 Replies
Oct 2, 2006
Hiok.. i have 3 tables, Product, Orders, OrderDetails.. orders isnt to important for thisOrder Details and Product contains ProductID, OrderDetails contains ProductID's and Quantitys for Products that have sold, not for products that havent soldthe Product table holds all productIDshow do i write a proc to display all products that havent sold??Cheers
View 5 Replies
View Related
Nov 3, 2006
hi
i'm trying to write a stored_proc which involves 3 tables. Product, Orders and OrderDetails
i want to return products that haven't sold for a particular month. So these products aren't in the OrderDetails table. When i try and run this i get some really random results. when i change the Orders.OrderDate value to different months, i get the same result when i shouldnt. but then when i add the Orders.OrderDate row to the select statement, i get 400 results and i dont have that many products. the extended amounts are just for my datagrid
ALTER PROCEDURE proc_Report_NoSales_Septasset nocount onSELECT Product.productID, Product.Title, Product.QtyOnHand, Product.Category, 0 as ExtendedAmount, Product.BuyPrice, 0 as ExtendedAmount2 from Orders, OrderDetails, Productwhere Orders.OrderDate > '2006-09-01' and Orders.OrderDate < '2006-10-01'and Orders.OrderID = OrderDetails.OrderID and Product.ProductID not in (Select distinct OrderDetails.ProductID from OrderDetails)/* Product.ProductID NOT EQUAL to OrderDetails.productID */and Product.productID <> OrderDetails.productIDGROUP BY Title, BuyPrice, Category, Product.ProductID, QtyOnHandORDER by TitleRETURN
any ideas
cheers!!!
View 2 Replies
View Related
