Calculate total cost from tbl1 & insert in tbl2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculate total cost from tbl1 & insert in tbl2

Author

Topic

Zephyr13
Starting Member

2 Posts

Posted - 2009-09-24 : 16:33:58

Hi

I'm busy with a project for my final year, and i need to do the orders part of the project. Its basically a customer thats logged in..they select items from a list of products and add it to a cart. From the cart they can delete items or insert more. Once they confirm that they want whats in the cart, the items must be inserted into the Order table, as well as the Order_product table because the prices of the items need to remain what they were when they placed the order.
The Order table has Order_ID, Customer_ID, Order_Date and Order_Cost.
The Product table has Product_ID, Product_Cost, name and description.
The Order_Product table has the Order_ID and the Product_ID as primary keys, and the Product_cost.
The Order_Cost from the Order table is the total cost of all the items that have been chosen from the cart.
How do i add the costs of all the items that have been chosen, and insert it as a total cost for one order?
For now i am accepting @Order_Cost as a parameter with value 0 just to see if its inserting, and it works, but as soon as i try to insert the total, its not working out. This is what i have:

ALTER procedure [dbo].[Insert_Order]
@Order_Date datetime,
@Cust_ID Varchar(15),
@Order_cost numeric(6,2)
AS
DECLARE @totcost numeric(6,2)

UPDATE Order_Table
SET @totcost = (SELECT SUM (Prod_cost) FROM Order_Product_table WHERE Order_table.Order_ID = Order_Product_table.Order_ID
AND Order_table.Cust_ID = @Cust_ID);

INSERT INTO Order_Table(Cust_ID, Order_Date, Order_cost)
VALUES(@Cust_ID, @Order_Date, @Order_cost);

I also tried using @totcost where @Order_cost is (in the VALUES bracket), but that didnt work either.
Please reply ASAP...i have to have this done before monday!

Zephyr13
Starting Member

2 Posts

Posted - 2009-09-24 : 16:41:16

Oh yes theres also a cart table..it has Customer_ID, Product_ID, and Qty in it. Customer_ID and Product_ID are both primary keys. I tried using the Cart table in my WHERE clause but it wasnt allowing me, it said it couldnt bound it...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-25 : 13:47:03

[code]UPDATE ot
SET ot.Order_Cost=oc.Total_Order_Cost
FROM Order ot
INNER JOIN
(SELECT op.Order_ID,SUM(p.Product_Cost * c.Qty) AS Total_Order_Cost
FROM Order_Product op
INNER JOIN Product p
ON p.Product_ID=op.Product_ID
INNER JOIN Cart c
ON c.Product_ID=p.Product_ID
GROUP BY op.Order_ID)oc
ON oc.Order_ID=ot.Order_ID
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources