Hi
ok.. i have 3 tables, Product, Orders, OrderDetails.. orders isnt to important for this
Order Details and Product contains ProductID, OrderDetails contains ProductID's and Quantitys for Products that have sold, not for products that havent sold
the Product table holds all productIDs
how do i write a proc to display all products that havent sold??
Cheers
Hi... when i run this procedureALTER PROCEDURE dbo.StoredProcedure3ASselect Title, Quantity, OrderDatefrom Orders, OrderDetails, ProductwhereOrderDate > '2006-09-01' and OrderDate < '2006-10-01'and Orders.OrderID = OrderDetails.OrderIDandProduct.productID = OrderDetails.productIDorder by titleI get this resultTitle 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 View Relatedhi
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!!!