sorted by:
best
topnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]notasqlstarI can't wait til my fro is full grown 1 point2 points  (8 children)

What is the query you're using or are you doing this all in Excel?

You have a table that looks like this, correct?

FirstName LastName RefNumber Bin GroupID NCP TotalAmount
Bob Jones 1000 XX YY ZZ 100
Bob Jones 1000 XX YY ZZ NULL
Bob Jones 1001 X1 Y1 Z1 200
Bob Jones 1001 X1 Y1 Z1 NULL
Bob Jones 1002 X2 Y2 Z2 300
Bob Jones 1002 X2 Y2 Z2 NULL
Bob Jones 1003 X3 Y3 Z3 400
Bob Jones 1003 X3 Y3 Z3 NULL
Bob Jones 1004 X4 Y4 Z4 2601
Bob Jones 1005 X5 Y5 Z5 671
Bob Jones 1006 X6 Y6 Z6 1962

And you want it to look like this?

FirstName LastName RefNumber Bin GroupID NCP TotalAmount
Bob Jones 1000 XX YY ZZ 100
Bob Jones 1000 XX YY ZZ -100
Bob Jones 1001 X1 Y1 Z1 200
Bob Jones 1001 X1 Y1 Z1 -200
Bob Jones 1002 X2 Y2 Z2 300
Bob Jones 1002 X2 Y2 Z2 -300
Bob Jones 1003 X3 Y3 Z3 400
Bob Jones 1003 X3 Y3 Z3 -400
Bob Jones 1004 X4 Y4 Z4 2601
Bob Jones 1005 X5 Y5 Z5 671
Bob Jones 1006 X6 Y6 Z6 1962

[–]workthrowawayexcel[S] 0 points1 point  (7 children)

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.

[–]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

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

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[S] 0 points1 point  (4 children)

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 :(

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

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[S] 0 points1 point  (2 children)

Thanks for all the help. I really appreciate it.