Trouble with Math - SQL Server Forums

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

Trouble with Math

Author

Topic

tranquilraven
Starting Member

19 Posts

Posted - 2012-06-26 : 12:28:33

I am having trouble with my math on this query. Here is what I am trying to achieve: I have Item price and quantities which are multiplied and then shipping and taxes are added to that result. The problem is that when a Item number is a duplicate, I want add the multiply the item prices times their quantity and then add those together, then I need to add the tax and shipping only once to that result. I can see it but I can't wrap my mind around it. Here is the code and my current result set:


SELECT
		o.OrderDate AS 'Order Date',
		od.OrderID AS 'Order #',
		od.ProductCode AS 'Item #',
		od.ProductName AS 'Description',
		od.Quantity,
		od.ProductPrice AS 'Item Price',
		o.TotalShippingCost AS 'Shipping//Handling',
		o.SalesTax1 AS 'Sales Tax',
		((CAST(od.ProductPrice AS Float) * CAST(od.Quantity AS Integer))  + CAST(o.TotalShippingCost AS Float) + CAST(SalesTax1 AS Float))AS 'Net Sale'
FROM Orders AS o
INNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID
WHERE PaymentDeclined IS NULL
Order by o.OrderDate DESC

order date	order #	item #	quantity	item price	shipping//handling	sales tax	net sale
6/25/2012 21:26	32744	Account	2	0	0	0	0
6/25/2012 21:10	32743	r-p52-0001	1	24.99	13.75	0	38.74
6/25/2012 21:09	32742	096-050609	1	39.99	14.1	0	54.09
6/25/2012 21:06	32741	097-080029	1	5.99	7.46	0.96	14.41
6/25/2012 21:06	32741	097-080008	1	5.99	7.46	0.96	14.41
6/25/2012 21:05	32739	P53-0005	1	0	0	0	0
6/25/2012 21:05	32740	R-P21-1030	1	59.99	23.47	0	83.46
6/25/2012 21:05	32738	096-050565	1	9.99	7.46	0	17.45
6/25/2012 21:02	32736	096-050609	1	39.99	9.98	0	49.97
6/25/2012 21:01	32735	090-020047	1	1.99	6.95	0	8.94
6/25/2012 21:01	32735	090-130022	1	1.99	6.95	0	8.94
6/25/2012 21:00	32734	Account	1	0	0	0	0
6/25/2012 20:55	32733	096-050610	1	35.99	8.35	3.15	47.49
6/25/2012 20:53	32732	096-050610	1	35.99	10.22	0	46.21
6/25/2012 20:49	32731	096-050611	1	29.99	14	0	43.99
6/25/2012 20:48	32730	096-050609	1	39.99	8.03	3.3	51.32
6/25/2012 20:45	32729	R-P10-1400F	1	19.99	13.43	1.35	34.77
6/25/2012 20:40	32728	096-050607	1	89.99	18.66	0	108.65
6/25/2012 20:40	32728	078-110218	1	12.99	18.66	0	31.65
6/25/2012 20:40	32728	078-110143	1	2.99	18.66	0	21.65

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-26 : 12:52:16

you have your answer in this thread [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=176033[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources