May 2, 2006 at 9:22 am
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
May 2, 2006 at 10:00 am
not sure this is exactly what you're looking for but how about
select amountid,count(*) from amount group by amountid where amountid>0
this should then have the same values in the resultset
select amountid,count(*) from amount group by amountid where amountid<0
you could joun the 2 results on the abs(amountid) and subtract
the negative count from the positive count.
or am i barking up the wrong tree - maybe there's an easier way
MVDBA
May 2, 2006 at 10:28 am
I have a question which should help us come up with an answer. In your example above, you wanted 3 and 6 returned, and while it's pretty clear how to return the 6, how do you differentiate between 3 and 1 above? What's to say that 2 is matching 1 instead of 3, in other words?
Additionally, are the negative numbers, when they exist, always the value of ID + 1 of their corresponding positive value? If so, that would answer my question above, and it would also make for an easy solution, as you could do an offset self-join.
May 2, 2006 at 10:35 am
The problem is that #3 does have a matching value in #2. SQL has no problem joining both #1 and #3 to #2. If you can place another rule on it, like the ID's are always incrementing you could add to your WHERE clause with something like:
AND b.id > a.id
May 2, 2006 at 11:27 am
I agree with Aaron, you have a data modeling issue here,
this query works but it requires the records to be inserted in the correct order which is not a good programming practice.
select AmountID, Amount
from #Amount A
where Amount > 0
and not exists (select *
from #Amount B
where b.amount < 0
and abs(B.Amount) = A.Amount
and A.AmountID < B.AmountID)
May 2, 2006 at 11:35 am
Someone posted a response on another forum that worked perfectly...
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 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
)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply