Figuring out which values don''t match up.

  • 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

  • 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

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

     

     

  • 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

  • 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)

  • 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