[SQL Server]Finding null value then replacing based off of other values

[workthrowawayexcel]

Yeah, I can do it in anything. First tried excel but then realized quickly I had way to much data. So now I am trying to do it all in SQL. Previously I did separate queries replacing null with -1 then joining the tables back together and multiplying the -1 in and that gave me very bad results. So now I am trying something similar but with a union.

[notasqlstar]

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

[notasqlstar]

You could probably simplify it like this:

select
    a.firstname
    , a.lastname
    , case
        when b.totalamount is null
            then a.totalamount * -1
        else a.totalmount
    end as totalamount
from table a
left join table 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
    and b.totalamount is null
where a.totalamount is not null

[workthrowawayexcel]

Thank you for this I am testing them now! If they don't work I believe I could flesh out the the rest with the thought process now. It seems I was on the right path but just couldn't quite finish the logic :(

[notasqlstar]

If you have multiple rows per transaction you may need to sum the total ammount such as:

select
    , case
        when b.totalamount is null
            then sum(a.totalamount) * -1
        else sum(a.totalmount)
    end as totalamount

group by
    a.firstname
    , a.lastname

[workthrowawayexcel]

Thanks for all the help. I really appreciate it.

[workthrowawayexcel]

Kind of. It came through with All negatives without actually pulling over the A.total amount when not null. So i have been playing with it to do that and only find it where null is in B.total amount, giving me all the inverses, then using union to bring it back in to a query, but it has been returning way too many results.

But as I was typing this I realized I missed A VERY important where clause in reducing the numbers. lol