Adding sales tax only once on duplicate orders

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

Adding sales tax only once on duplicate orders

Author

Topic

tranquilraven
Starting Member

19 Posts

Posted - 2012-06-21 : 12:16:20

I am stumped. Here is my query:



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

Here is the Output:


order date	order #	item #	description	quantity	item price	shipping//handling	sales tax	net sale
5/4/2012 12:32	5742	096-050607	F2000C GFCI Pump	1	89.99	6.81	8.1	104.9
5/4/2012 12:38	5744	090-380006	Horizontal Beam for Metal Frame Round Pools 14' and up Part # 090-380006	5	9.99	5.99	0	55.94
5/4/2012 12:38	5744	097-080030	Pin with Grommet for Metal Frame Round Pools 14' and up. (10 Ea. per Bag) Part #: 097-080030	1	5.99	5.99	0	11.98
5/4/2012 12:38	5744	090-380056	T-Fitting for 18' X 48", 18' X 52" Metal Frame Round Pools part # 090-380056	5	7.99	5.99	0	45.94
5/4/2012 12:43	5745	078-110143	Pump Retaining Nut - Part #: 078-110143	1	2.99	5.99	0	8.98
5/4/2012 13:09	5751	090-010104	Hex Head Screws for all Metal Frame Round Pools.(12 EA.) Part #: 090-010104	1	1.99	5.99	0	7.98
5/4/2012 14:04	5752	078-110222	Strainer Basket for SFS 600/1000/1500 skimmer canisters. Part #: 078-110222	2	2.99	5.99	0	11.97
5/4/2012 14:10	5753	096-050610	F700C GFCI Pump	1	0	0	0	0
5/4/2012 14:10	5754	096-050609	F1000C GFCI Pump	2	39.99	8.62	5.4	94
5/4/2012 14:13	5755	078-110110	Pump Retaining Nut 078-110110	1	2.99	5.99	0	8.98
5/4/2012 14:15	5756	097-201201	Leg Cap for all Pro Series Metal Round Pools 22' and up. (*****5 Ea. per bag*****) Part #: 097-201201	5	5.99	5.99	0	35.94
5/4/2012 14:15	5756	090-380092-1	Horizontal Beam (Grey) for all Pro Series Round Frame Pools 22' and up Part # 090-380092-1	3	10.99	5.99	0	38.96
5/4/2012 14:15	5757	097-080002	Leg Cap for all Metal Frame Round Pools 14' and up. (5 Ea. per Bag) Part #: 097-080002	2	5.99	5.99	0	17.97
5/4/2012 14:17	5758	Account	General Consumer Account	1	0	0	0	0
5/4/2012 14:18	5759	097-080002	Leg Cap for all Metal Frame Round Pools 14' and up. (5 Ea. per Bag) Part #: 097-080002	4	5.99	5.99	0	29.95
5/4/2012 14:24	5760	096-050620	SFS1000 Assembled Canister with Pump	1	49.99	7.9	0	57.89
5/4/2012 14:25	5761	096-050609	F1000C GFCI Pump	1	39.99	6.81	0	46.8
5/4/2012 14:28	5762	096-050609	F1000C GFCI Pump	1	39.99	6.81	2.7	49.5
5/4/2012 14:29	5763	096-050609	F1000C GFCI Pump	1	39.99	11.85	3.1	54.94
5/4/2012 14:29	5763	097-080030	Pin with Grommet for Metal Frame Round Pools 14' and up. (10 Ea. per Bag) Part #: 097-080030	1	5.99	11.85	3.1	20.94

What I want to accomplish is to make sure that duplicate order numbers multiply the item price and quantity, but only add the shipping and sales tax on the first order number (shipping and sales tax are the same on duplicate orders). I see it in my head but cannot figure it out. Any help or explanation would be greatly appreciated.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-21 : 13:50:41

That is a display problem and you should handle that in your front end if possible.
In SQL it is correct to repeat that values each time the same order is returned.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-06-21 : 16:17:57

I'd agree that is a display issue. It appears that you put the sales tax for the entire order at the order level and no the detail/line item level. There are several possibilities if you still want to tackle this in SQL. One option would be to use a ranking function (like ROW_NUMBER()) and only display the sales tax for row_number = 1. Another option would be to calculate the total for a group (order) and do some math to calculate the sales tax for that line item, but that'd probably introduce rounding errors that'd cause issues if you are off by a cent or two.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources