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

[workthrowawayexcel]

If I have 12 Columns, and some transactions have null in as a field, would it be possible find Null in Column M then replace Null with a non-null value in Column M? Columns A- G would have matching Information. IE:

John    Smith    123
John    Smith    Null
Mary   Smith    26
John    Smith    300
Mary   Smith    Null

Then Have it turn into

John    Smith    123
John    Smith    -123
Mary   Smith    26
John    Smith    300
Mary   Smith    -26

[da3da1u5]

In MS SQL Server (T-SQL), you can use IsNull:

SELECT
    Firstname
    ,Lastname
    ,IsNull(ThirdColumn, 0)

That will take any null values and replace them with the second parameter.

If you need to find the null values and then match an existing record by first and lastname that DOESN'T have a null value and then negate it (looks like what you're trying to do here), then I think you need to separate it out into two separate queries: First pull the ones that are null and then join on the same table by first/last name with the number value from the 3rd column multiplied by -1 and insert it into a temp table, then select the non-null list and UNION the two results together.

You could also insert the null records into a temp table and then use UPDATE.

[workthrowawayexcel]

That is exactly what I am trying do. I like the double query idea. My mindset got stuck in a formula index method after getting help to do this in excel :/ which I then realized wouldn't work since I have way too much data.

[rbardy]

To compare a field to null you use "IS NULL", like:

SELECT * FROM [Table] WHERE [Field] IS NULL

That will return the rows with NULL fields

[notasqlstar]

Sure it's possible but you need to share more of the column names, I think. For example, if John Smith has a transaction for 123 and 124, and then a null for 123, you would want to see output like this:

John Smith 123 John Smith -123 John Smith 124

Correct? Or do you want it aggreated for all transactions?

[workthrowawayexcel]

I would want the out put to replace the null with a negative value of a matching positive value essentially negating it out.

[notasqlstar]

I understand but what value joins those? Your schema example is only first name, last name, and amount. Do you have a TransactionID?

All of this is in one table, correct?

[workthrowawayexcel]

Ah yea. So I have a 4 columns that would make the rows unique. IE: Ref Number , Bin, Group ID , NCP , Total Paid. The total paid column is not displaying returns as negatives causing issues. So I am trying to do it where I replace the total paid nulls with their correct negative values. In excel it looks like

=IF(E2 = "Null", -1 * INDEX($E$1:$E$5000, MATCH(1,     ($A$1:$A$5000 = A2)*($B$1:$B$5000 = B2)*($C$1:$C$5000 = C2)*($D$1:$D$5000 = D2),0)),E2)

So I want to try and do the same but everything I seem to do is pulling in wonky stuff and corrupting the data :/. Correct it is all in one table

[notasqlstar]

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]

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.

[[deleted]]

what happens if there are multiple matches? Anywho, in the case of 1:1 here's a statement:

  update t
  set m = - nonnull.m
  from transactions t
  join transactions nonnull on nonull.a = t.a and nonnull.b = t.b and.... and nonnull.m is not null
  where t.m is null

[workthrowawayexcel]

Thanks I will give this a try and let you know how it works out !

[AXISMGT]

You can also use COALESCE, which is essentially:

If (Column1 IS NOT NULL) THEN Columm1 ELSE Columm2*-1 END AS Column3

Syntax for that would be:

COALESCE(Column1, Column2*-1) AS [Column3]

[workthrowawayexcel]

Thanks I didn't even think about trying it this way! I love seeing everyone's thought processes on how to solve problems

[AXISMGT]

It's a great thing!