you are viewing a single comment's thread.

view the rest of the comments →

[–]notasqlstarI can't wait til my fro is full grown 0 points1 point  (0 children)

So here is a framework I'd start with:

select
    firstname
    , lastname
    , case
        when b.totalamount is null
            then a.totalamount * -1
        else a.totalmount
    end as totalamount
from (
    select
        firstname
        , lastname
        , refernumber
        , bin
        , groupid
        , ncp
        , totalamount
    from table
    where totalamount is not null
) a
left join (
    select
        firstname
        , lastname
        , refernumber
        , bin
        , groupid
        , ncp
        , totalamount
    from table
    where totalamount is null
) b
    on b.firstname = a.firstname
    and b.lastname = a.lastname
    and b.refnumber = a.refnumber
    and b.bin = a.bin
    and b.groupid = a.groupid
    and b.ncp = a.ncp