CODEWITHSUNDEEP
sqlsql-server
Arjun
Arjun

Reputation: 1069

Programming with COALESCE function

I have a table like this

ac  asg     asgc    asgdt
1   abc     abc     2012-06-01 00:00:00.000
1   NULL    NULL    2012-06-02 00:00:00.000
1   xyz     xyz     2012-07-01 00:00:00.000
1   NULL    NULL    2012-07-02 00:00:00.000
2   NULL    NULL    2012-07-03 00:00:00.000
2   lmn     lmn     2012-08-01 00:00:00.000
2   NULL    NULL    2012-08-02 00:00:00.000

I have to remove nulls by repeating previous text, so I wrote 

Declare @asgc nvarchar(10)
UPDATE coalescetest 
SET 
    @asgc = COALESCE(asgc, @asgc),
    asgc = COALESCE(asgc, @asgc)

This code gave me below output

ac  asg     asgc
1   abc     abc
1   NULL    abc
1   xyz     xyz
1   NULL    xyz
2   NULL    xyz
2   lmn     lmn
2   NULL    lmn

Problem here is, it should repeat previous text at account level. As it is seen, 'xyx' value for ac 1 is repeated to ac 2. This should not happen. Ideal output should be like this

ac  asg     asgc
1   abc     abc
1   NULL    abc
1   xyz     xyz
1   NULL    xyz
2   NULL    NULL
2   lmn     lmn
2   NULL    lmn

So, I wrote a loop at ac level. But it is killing performance. Can anyone please suggenst a way out. Thanks in adavance.

Upvotes: 2

Views: 285

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

This works:

declare @tab table (ac int not null, asg char(3) null, asgc char(3) null, asgdt datetime not null)
insert into @tab(ac,asg,asgc,asgdt) values
(1,'abc','abc','2012-06-01 00:00:00.000'),
(1,NULL,NULL,'2012-06-02 00:00:00.000'),
(1,'xyz','xyz','2012-07-01 00:00:00.000'),
(1,NULL,NULL,'2012-07-02 00:00:00.000'),
(2,NULL,NULL,'2012-07-03 00:00:00.000'),
(2,'lmn','lmn','2012-08-01 00:00:00.000'),
(2,NULL,NULL,'2012-08-02 00:00:00.000')

update
    t1
set
    asgc = t2.asgc
from
    @tab t1
        inner join
    @tab t2
        on
            t1.ac = t2.ac and   --Belong to same account
            t2.asgc is not null and --Has a useful value
            t2.asgdt < t1.asgdt   --Is an earlier row
        left join
    @tab t3
        on
            t1.ac = t3.ac and   --Belong to same account
            t3.asgc is not null and --Has a useful value
            t3.asgdt < t1.asgdt and   --Is an earlier row
            t3.asgdt > t2.asgdt   --But occurs later than t2
where
    t1.asgc is null and --Needs a fill-in value
    t3.ac is null   --And no better matching row was found for the replacement

select * from @tab

Result:

ac          asg  asgc MysteriousUnamedColumn
----------- ---- ---- -----------------------
1           abc  abc  2012-06-01 00:00:00.000
1           NULL abc  2012-06-02 00:00:00.000
1           xyz  xyz  2012-07-01 00:00:00.000
1           NULL xyz  2012-07-02 00:00:00.000
2           NULL NULL 2012-07-03 00:00:00.000
2           lmn  lmn  2012-08-01 00:00:00.000
2           NULL lmn  2012-08-02 00:00:00.000

Note that, at no point, am I relying on what order the UPDATE is actually applied to the table.

Just realised that my answer, of course, doesn't actually use COALESCE, as per the title of the question. But, TBH, it was the wrong tool for the job at hand anyway. You could re-write the above query to use a COALESCE, and have it update all rows rather than just those with NULL values, but I can't think of any sane reason to do so.

Upvotes: 5

Related Questions