Loops, Addition, and Multiply in a Stored Proc

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

SQL Server 2000 Forums

SQL Server Development (2000)

Loops, Addition, and Multiply in a Stored Proc

Author

Topic

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-10 : 21:10:29

Hi all,
I think this is a simple problem but im not sure how to do it.
I have 2 tables. And basicly I need to find out the difference in price between the Grandtotal in 'Orders' and the total from 'OrderDetails'. I have never done loops before so Im not sure what to do.

 
Order details and Orders

Order_Details
orderID int,
quantity int,
PriceperUnit money

Order
OrderID int,
Grandtotal money

and in those tables you may have something like this.


Orders
---------------
orderID  | GrandTotal |
13          270.99
14          289.35

OrderDetails
---------------
orderID  | quantity | priceperunit |
13         3           27.95
13         1            57.97
14         33           .97
15         1            1.97

So basicly i need to
add all the (priceperunit * quantity) that orderid = (lets say 13)
then subtract it from Orders.Grandtotal.

Can someone clue me in to how to do this in a stored Proc?

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-10 : 21:43:47

i have this solution but I dont know if its the fastest.

Select  (SELECT sum(Quantity * UnitCost) as subtotal
FROM V020U18NUC_AGC.OrderDetails
where V020U18NUC_AGC.OrderDetails.OrderID = '13')-(Select GrandTotal from V020U18NUC_AGC.Orders where V020U18NUC_AGC.Orders.OrderID = '13') as total

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-10 : 21:47:49

SELECT
OrderID,
GrandTotal,
DetailTotal,
GrandTotal - DetailTotal as Difference
FROM
Order
INNER JOIN
(SELECT OrderID, SUM(quantity * Priceperunit) as DetailTotal FROM Order_Details) OD
ON
Order.OrderID = OD.OrderID

- Jeff

Johnhamman
Starting Member

37 Posts

Posted - 2002-12-11 : 00:12:33

Thanks Jeff, Im getting a error still,

Ambiguous column name 'OrderID'.

This is the code


SELECT 
OrderID, 
GrandTotal, 
DetailTotal, 
GrandTotal - DetailTotal as Difference 
FROM 
V020U18NUC_AGC.Orders
INNER JOIN 
(SELECT OrderID, SUM(UnitCost * Quantity) as DetailTotal FROM  V020U18NUC_AGC.OrderDetails) OD 
ON 
V020U18NUC_AGC.Orders.OrderID = OD.OrderID

Edited by - johnhamman on 12/11/2002 01:18:44

nr
SQLTeam MVY

12543 Posts

Posted - 2002-12-11 : 07:09:40

SELECT
V020U18NUC_AGC.Orders.OrderID,
GrandTotal,
DetailTotal,
GrandTotal - DetailTotal as Difference
FROM
V020U18NUC_AGC.Orders
INNER JOIN
(SELECT OrderID, SUM(UnitCost * Quantity) as DetailTotal FROM V020U18NUC_AGC.OrderDetails) OD
ON
V020U18NUC_AGC.Orders.OrderID = OD.OrderID

Would be better to qualify all the columns with the table name in case you add another table to this query in the future.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-11 : 08:25:01

Oops -- thanks Nr, forgot that OrderId was ambigious.

John -- the error was that there were two orderID's in different parts of the query, so we needed to specify which one we wanted to use in the SELECT. Even though they are the same because they are JOINed together.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources