duplicates / reversals problem

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

Transact-SQL (2000)

duplicates / reversals problem

Author

Topic

calculon
Starting Member

3 Posts

Posted - 2004-02-05 : 03:26:52

I'm trying to remove the all the reversals without removing the legitimate rows. So if there's a negative row, there will be a postive row. It doesn't matter which postive row is removed, just as long as the pair, postive and negative, rows are remove. I can't seem to get an inner join with distinct to work without getting too many sets.

So there should be two 4.34's and three 10.96's after the query is run.

CREATE TABLE [orders] (
[Id] [int] NULL,
[ProductId] [char] (11) NULL,
[OrderID] [int] NULL,
[Date] [smalldatetime] NULL,
[Qty] [int] NULL,
[Price] [smallmoney] NULL
)
GO

insert into orders values(34533, 264363, '24452', '20030103', 3, 4.34)
insert into orders values(76635, 264363, '24452', '20030103', 3, 4.34)
insert into orders values(456634, 264363, '24452', '20030103', 3, -4.34)
insert into orders values(46742, 264363, '24452', '20030103', 3, -4.34)
insert into orders values(46788, 264363, '24452', '20030103', 3, 4.34)
insert into orders values(46691, 264363, '24452', '20030103', 3, 4.34)

insert into orders values(56334, 7895, '24572', '20030103', 1, -10.96)
insert into orders values(56336, 7895, '24572', '20030103', 1, -10.96)
insert into orders values(56337, 7895, '24572', '20030103', 1, 10.96)
insert into orders values(56444, 7895, '24572', '20030103', 1, 10.96)
insert into orders values(56454, 7895, '24572', '20030103', 1, 10.96)
insert into orders values(56784, 7895, '24572', '20030103', 1, -10.96)
insert into orders values(56794, 7895, '24572', '20030103', 1, 10.96)
insert into orders values(565895, 7895, '24572', '20030103', 1, 10.96)
insert into orders values(576999, 7895, '24572', '20030103', 1, 10.96)

Lewie
Starting Member

42 Posts

Posted - 2004-02-05 : 04:08:15

Might work... just check

delete from orders
from orders join
(Select Min(a.id) z,Min(b.id) y, a.ProductId Apid,a.orderid Aoid
from orders a join orders b
on a.Productid = b.productid and a.orderid = b.orderid and a.id <> b.id
AND a.Price >0 and b.price < 0 and a.price = abs(b.price)
group by a.ProductId,a.orderid) C
ON Apid = ProductID and Aoid = orderid and (z <> id and y <> id)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-02-05 : 04:50:01

It seems that you might need to join to another table here, which can help you identify which reversals where for which transactions.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-02-05 : 07:32:59

Regretably, I think you may need to use the dreaded-cursor for a solution to this. You may be able to work with a WHILE statement as well....but I'm not sure if it would be any simpler/faster than the cursor.

The main problem you are going to get is that, the act of establishing one-pairing, affects the rest of your working-data-set. And thus the calculation/action for the 2nd/3rd, etc pairings is dependant on the result of the previous pairing....and this is not compatible with set-based operations.

Take the following example....with your sample data for order '24452'

The 1st row (34533, 264363, '24452', '20030103', 3, 4.34) can be paired with
either (456634, 264363, '24452', '20030103', 3, -4.34) or
( 46742, 264363, '24452', '20030103', 3, -4.34)

let's say for example it gets paired with the lower id...ie a first-in-first-out scenario...then
when it comes to repeating the exercise for #2 (76635, 264363, '24452', '20030103', 3, 4.34), the potential set against which it will be matched...is one smaller than that available to the previous match...ie (456634, 264363, '24452', '20030103', 3, -4.34) only.

So here, the successful pairing attempt for the 1st row affects the result for the 2nd row.

Because T-SQL processes data in sets....the sets of data that it works with are established at the start of the run, and cannot dynamically alter half-way down the run as the result of previous calculations within the set.

(I hope I'm not giving you a bum-steer on this one, but i've had to solve this style of problem myself recently...and I was unable to come up with any solution other than a cursor-style one. If anybody can give a definitive set-based solution...i would be interested in being informed)

As a total aside....the code above from Lewie can be made to execute faster (I'm not sure if the code will actually work in all situations)...by replacing "a.id <> b.id" with "a.id < b.id"..the latter will reduce the work-set by a significant amount.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-05 : 08:33:17

Not sure I like this but it works:


select  d1.id,d1.productid,d1.orderid,d1.date,d1.qty,d1.price
from
(
	select top 100 percent *, (select count(1) from orders where productid = o.productid and id <= o.id and price > 0) recs
	from orders o
	where price > 0
	order by productid, id, price
) d1

join
(
	select d1.productid,d1.neg_row, d2.pos_row, d2.pos_row - d1.neg_row as recs
	from
	(
		select productid,count(id) as neg_row
		from orders 
		where price < 0
		group by productid
	) d1
	join
	(
		select productid,count(id) as pos_row
		from orders
		where price > 0
		group by productid
	) d2 on d2.productid = d1.productid

) d2 on d2.productid = d1.productid and d2.recs >= d1.recs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources