derived column

[skarosi]

Hi all,
I am quite new to the SQL Server, even though i have done DBs in uni, i havent really used them so much. any how, i need some help
I have a table with products and a table with orders. on the products table i have the product ID and the price of each product.
on the orders table the product ID is inserted and the quontity of the order. I want when ever i insert a new record of order, to automatically calculate the total cost. for example i order 10 pieces of product1, costing 10(any currency) each, so total cost would be 100!
I dont know if that is possible but i think it should, its not that difficult, is it?
I saw the "formula" option on the create table window, but i just cant get it to work.
any help?
thanks

 

[ptheriault]

I think you might have better luck with this if you use a trigger to update your price. If you are not familiar with triggers look them up in Books on-line.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[SQLSister]

the calculated column is the way to go, it is faster than a trigger.
assume your columns inthe table ar quantity and price
create a new field called totalprice and make it a decimal data type. Then inthe formula spot enter
quantity * price

That should do it unless your quantity and price columns are not some kind of numeric data. If they are int you may need to cast the fieldname inthe formula to decimal for the calcualtion to be correct. Make sure you have more decimal places than the max the two columns have because a multiply will give you more decimial places.

Questions about posting. See faq183-874

 

[skarosi]

Will that work if the two number (quantity and price on your example) are on different tables? thats what i did but it dint work

 

[nice95gle]

What you need is a View.
It will look something like this

create view "Total_Sales"
AS
SELECT a.ProductID, a.Quality, b.Price, b.Price*a.Quality AS Total
FROM Orders a 
LEFT OUTER JOIN Products b 
ON a.ProductID= b.ProductID
GO

Well Done is better than well said
- Ben Franklin

 

[SQLSister]

If they are on different tables you may be stuck with a trigger or as nice95gle says a view. the concern I have about using a view for this is if the tables are large, you wouldn't want to be doing that calculation everytime you open the view for all the records.

Questions about posting. See faq183-874

 

[skarosi]

i would prefare not to use view. I will give it a try to use a trigger, dont know how to do it, but i suppose i will find something. thanks

 

[nice95gle]

Maybe the anwser is not to store the calculations in SQL server at all. Can this work be done on the front-end? Even if this is needed for a report, I can't think of any reporting tool that can't do the math.

Let us know why to need to store this data in the table and maybe we can help you decide on an alterative.



Well Done is better than well said
- Ben Franklin

 

[ptheriault]

You trigger would look something like this.

create trigger calc_total
on test
for insert
as
BEGIN

declare @prodid int

select @prodid = Productid from inserted

UPDATE orders
set total = b.Price * b.Quantity
FROM products b
WHERE Productid = @prodid

END

(not tested)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[skarosi]

Ok, lets explain what i want to do.
it is an example of a company DB. I have a table for the customers, the products, the suppliers and the employees. there is also a table for taking orders, in witch the productID and the quantity are entered. the price of the product is ofcourse stored on the "product" table.
I would like to see the cost of the order (product price* quantity) when ever a new order is placed. I thought that it would be nice to do it automatically, but if it is a trouble, i think that i might as well add an other column with the product price on the order table and do the multiplication with that.
is it at least possible to get the price from the other table with out me looking manually on the other table?
thanks ptheriault, i will give it a try.