get rid of duplicate postage - 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

get rid of duplicate postage

Author

Topic

Movember
Starting Member

12 Posts

Posted - 2009-11-16 : 00:36:51

Trying to run a query where I am trying to do a sales reconcile for the last week. The problem is it adds the postage cost to both lines if an order has multiple products. Is there a way to stop it from doing this if it sees multiple items on the one order?

Thanks

order product cost postage
1234 xx $12 $15
1234 xy $20 $15 (don't want this to appear)

Sachin.Nand

2937 Posts

Posted - 2009-11-16 : 01:11:15

What version of SQL server r u using?

PBUH

Movember
Starting Member

12 Posts

Posted - 2009-11-16 : 06:37:26

Using SQL2008

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-11-16 : 07:10:31

select * from
(select row_number()over(partition by order,product,postage order by order)as rid,
* from tablename) where rid = 1

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-16 : 11:56:34

quote:
Originally posted by bklr

select * from
(select row_number()over(partition by order,product,postage order by order)as rid,
* from tablename) where rid = 1

Well that's a hack to say the least...what if they reorg everything, or the path to the data changes

Your results would be variable

What elese do you have in the table?

And I see if you have different values on your denormalized mess of a table

How do you know which one you should be using....and don't say because it comes out that way in a result set

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Movember
Starting Member

12 Posts

Posted - 2009-11-16 : 16:45:13

The query I have is this so far.

select orders.orderid,
products.ProductID,
title,
price [Cost],
postage,
(orderentry.price+shoporders.postage),
entry.units,
(entry.price+orders.postage)*sum(entry.units) [Order Total]
from WEB_Shop_Orders orders
left outer join WEB_Shop_Orders_Entry entry on entry.OrderID=orders.OrderID
left outer join WEB_Shop_products products on Products.ProductID=entry.ProductID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources