Figuring out which values don't match

ninel · May 2nd, 2006, 10:27 AM

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

Code:

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:

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

Jeff Mason · May 2nd, 2006, 05:50 PM

The row with ID = 3 does in fact have a matching row of opposite sign, i.e. the row where ID = 2.

Why does row ID = 1 match row 2, but ID = 3 doesn't?

I'm guessing you are looking to somehow "pair up" values, but I see no way to do that with this table design. These sorts of things are done typically with two columns, one for the debit amount, and one for the credit. The values are thus "paired up" by virtue of being in the same row. Presumably, elsewhere in the same row is a column which identifies what these debit and credit amounts refer to, such as a general ledger account, or similar thing.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Peso · May 8th, 2006, 07:27 AM

DECLARE @Temp TABLE
(
Amount NUMERIC(10, 2),
Items INT,
AmountID INT
)

INSERT INTO @Temp
(
Amount,
Items,
AmountID
)
SELECT CASE WHEN COUNT(#Amount.Amount) < 2 * (SELECT COUNT(*) FROM #Amount A WHERE ABS(#Amount.Amount) = A.Amount) THEN MAX(#Amount.Amount) ELSE MIN(#Amount.Amount) END AS Amount,
CASE WHEN COUNT(#Amount.Amount) < 2 * (SELECT COUNT(*) FROM #Amount A WHERE ABS(#Amount.Amount) = A.Amount) THEN 2 * (SELECT COUNT(*) FROM #Amount A WHERE ABS(#Amount.Amount) = A.Amount) - COUNT(#Amount.Amount) ELSE COUNT(#Amount.Amount) - 2 * (SELECT COUNT(*) FROM #Amount A WHERE ABS(#Amount.Amount) = A.Amount) END AS Items,
MAX(#Amount.AmountID)
FROM #Amount
GROUP BY ABS(#Amount.Amount)
HAVING SUM(#Amount.Amount) <> 0.0

DECLARE @Items INT

SELECT @Items = MAX(Items)
FROM @Temp

WHILE @Items > 1
BEGIN
INSERT INTO @Temp
(
Amount,
AmountID
)
SELECT T.Amount,
(SELECT MAX(#Amount.AmountID) FROM #Amount WHERE #Amount.Amount = T.Amount AND #Amount.AmountID NOT IN (SELECT T.AmountID FROM @Temp T))
FROM @Temp T
WHERE T.Items >= @Items

SELECT @Items = @Items - 1
END

SELECT AmountID,
Amount
FROM @Temp
ORDER BY AmountID

manudutt · May 8th, 2006, 01:45 PM

i agree with jeff , well what peso said may work , but it seems quite big for just a simple query ..

I have known a great many troubles, but most of them never happened. -- Mark Twain

robprell · May 9th, 2006, 06:08 PM

Another option is to select the absolute value of the amounts amounts into a temp table then use that temp table to join on the absolute value of your source table. Requires a temp table but the logic and code is easy to write. If you want me to write the code let me know.

Peso · May 10th, 2006, 01:53 AM

I don't think that approach works with two positive values without the matching negativa value, such as the data contains two 20.20 values.

Both of them should be considered wrong since neither of them has a matching negative value.

robprell · May 10th, 2006, 04:18 PM

So if there are two posative matching values and no negative it does not meet his criteria?

Peso · May 12th, 2006, 01:26 AM

If there are two +20.20 values and no -20.20 value, both +20.20 values should be considered erraneous. Because none of them can be matched with their counterpart negative number.