Figuring out which values don't match

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)

Figuring out which values don't match

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 11:23:07

I have 2 fields in a table: an Id and an amount field.


CREATE  TABLE AMOUNT(
AmountId [int] IDENTITY (1, 1) NOT NULL ,
Amount numeric(10,2) NULL)

INSERT #AMOUNT (Amount)
VALUES(125.23)

INSERT #AMOUNT (Amount)
VALUES(-125.23)

INSERT #AMOUNT (Amount)
VALUES(125.23)

INSERT #AMOUNT (Amount)
VALUES(33.45)

INSERT #AMOUNT (Amount)
VALUES(-33.45)

INSERT #AMOUNT (Amount)
VALUES(20.20)

So the data in the table will look like this:
Id AmountId
1 125.23
2 -125.23
3 125.23
4 33.45
5 -33.45
6 20.20

I need a query that returns records 3 and 6 becuase they don't have matching negative values.

I used the following code:


SELECT distinct  a.*, b.Amount, b.Amountid
FROM AMOUNT a
LEFT OUTER JOIN  AMOUNT b
 ON a.amount =  (b.amount *-1)
WHERE b.AmountId is NULL

This returns the 20.20, but doesn't return the 2nd 123.25.

Can anyone help me with this?

Thanks,
Ninel

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-02 : 11:27:12

SELECT distinct a.*, b.Amount, b.Amountid
FROM AMOUNT a
FULL OUTER JOIN AMOUNT b
ON a.amount = (b.amount *-1)
WHERE b.AmountId is NULL
or a.AmountId is NULL

==========================================
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.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 11:28:22

Is the definition of the "problem" that the negative value will come immediately after the positive value? As I see just the input how do I know that record 2 -125.23 doesn't match with record 3, so I should get Row 1 and row 6? Will it always be immediately after, or could record 18 contain a -125.23 that would map to record 3?

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 11:31:41

nr, that returns basically the same thing and still excludes the 125.23 which comes a second time. The Distinct is simply finding unique values and not taking the "counts" for them into account.

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 11:35:32

nr,
You're query is returning the following:
AmountId Amount Amount AmountId
NULL NULL 20.20 5
5 20.20 NULL NULL

I need to see:
5 20.20
6 123.25

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-02 : 11:35:58

You probably also should match on the count of the values in case there are 2 positives with one negative.
The usual way to do this is to assign a match id to the rows then output any that are left over but you could.

select * from amount where abs(amount) in
(SELECT distinct coalesce(a.amount, b.amount)
FROM (select amount, num = count(*) AMOUNT from amount where amount < 0 group by amount) a
FULL OUTER JOIN (select amount * -1, num = count(*) AMOUNT from amount where amount < 0 group by amount) b
ON a.amount = b.amount
and a.num = b.num
WHERE b.Amount is NULL
or a.Amount is NULL
)

==========================================
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.

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 11:36:33

I'm sorry... I meant
Id Amount
3 125.23
6 20.20

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-02 : 11:38:14

quote:
Originally posted by ninel

nr,
You're query is returning the following:
AmountId Amount Amount AmountId
NULL NULL 20.20 5
5 20.20 NULL NULL

I need to see:
5 20.20
6 123.25

SELECT Amountid = coalesce(a.Amountid, b.Amountid),
Amount = coalesce(a.Amount, b.Amount)
FROM AMOUNT a
FULL OUTER JOIN AMOUNT b
ON a.amount = (b.amount *-1)
WHERE b.AmountId is NULL
or a.AmountId is NULL

==========================================
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.

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 11:39:36

nr,
num = count(*) Are you placing the count into a varaible?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-02 : 11:41:53

Nope, it's just getting the count of the rows with that amount so that can be matched too.
I made a few mistakes in the query which I've corrected (still haven't tried it though).

==========================================
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.

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 11:43:50

Its still giving me 2 records of 20.20.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-02 : 11:53:10

The following will show you the two values that don't have matches, but not the ID's of the values since you still never answered my question about the basic definition and there is nothing inherent in the data to indicate that the 1 value of 125.23 matches either or the other ID value.[CODE]
declare @pos table (value numeric(10,2), cnt int)
declare @neg table (value numeric(10,2), cnt int)

insert into @pos
select amount, count(*) from #amount
where amount > 0 group by amount

insert into @neg
select amount, count(*) from #amount
where amount < 0 group by amount

select p.value
from @pos p left join @neg n on p.value = n.value * -1.0
where (n.value is not null and p.cnt > n.cnt)
or (n.value is null)[/CODE]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 12:02:48

Thanks. That did work.
Ans the negative value will not always immediatley follow the positive value.

Is there any way to do this within one query?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-02 : 12:36:11

An enhancement of my previous query (and tested).

select * from #amount where amountID in
(SELECT distinct coalesce(a.amountID, b.amountID)
FROM (select a.amountID, a.amount, num = (select count(*) from #amount a2 where a2.amount = a.amount and a2.amountId <= a.AmountID) from #amount a where amount < 0 group by amountId, amount) a
FULL OUTER JOIN (select a.amountID, amount = a.amount * -1, num = (select count(*) from #amount a2 where a2.amount = a.amount and a2.amountId <= a.AmountID) from #amount a where amount > 0 group by amountId, amount) b
ON a.amount = b.amount
and a.num = b.num
WHERE b.amountID is NULL
or a.amountID is NULL
)

==========================================
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.

ninel
Posting Yak Master

141 Posts

Posted - 2006-05-02 : 12:40:54

Thank you so much.
I really appreciate all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources