>>I have a table with null values in fields here and there. I would like a query that fills these in with values from the most recent record with something in that field. For example, if I have:
>>
>>field1 field2 field3
>>
>>3 5 6
>>5 6
>>7 2
>> 9
>>2 1 8
>>3 2
>>
>>I want:
>>
>>field1 field2 field3
>>
>>3 5 6
>>5 6 6
>>7 6 2
>>7 6 9
>>2 1 8
>>3 2 8
>>
>>
>>Thank you.
>
>
Okay, so I spent a little more time and here is a SINGLE SQL SELECT command that will return EXACTLY what was specified (including the recno column).
select a.nrecno,
field1 = CASE a.field1
WHEN NULL then final.field1a
ELSE field1 end,
field2 = CASE a.field2
WHEN NULL then final.field2a
ELSE field2 end,
field3 = CASE a.field3
WHEN NULL then final.field3a
ELSE field3 end
FROM
(
SELECT f1.nrecno, field1a, field2a, field3a
FROM
(
SELECT f1a.nrecno, field1a = bb1.field1
FROM
(
SELECT b1b.nrecno, maxnrecno = max(b1a.nrecno)
FROM test b1a , test b1b
WHERE b1a.field1 IS NOT NULL and b1a.nrecno < b1b.nrecno
group by b1b.nrecno
)
as f1a, test bb1
WHERE f1a.maxnrecno = bb1.nrecno
)
as f1,
(
SELECT f2a.nrecno, field2a = bb2.field2
FROM
(
SELECT b2b.nrecno, maxnrecno = max(b2a.nrecno)
FROM test b2a , test b2b
WHERE b2a.field2 IS NOT NULL and b2a.nrecno < b2b.nrecno
group by b2b.nrecno
)
as f2a, test bb2
WHERE f2a.maxnrecno = bb2.nrecno
)
as f2,
(
SELECT f3a.nrecno, field3a = bb3.field3
FROM
(
SELECT b3b.nrecno, maxnrecno = max(b3a.nrecno)
FROM test b3a , test b3b
WHERE b3a.field3 IS NOT NULL and b3a.nrecno < b3b.nrecno
group by b3b.nrecno
)
as f3a, test bb3
WHERE f3a.maxnrecno = bb3.nrecno
)
as f3
where f1.nrecno = f2.nrecno and f3.nrecno = f2.nrecno
)
as final, test a
where final.nrecno = a. nrecno
union
select nrecno, field1, field2, field3
from test
where test.nrecno = 1
ORDER by a.nrecno
And here are the actual results from SQL Server
nrecno field1 field2 field3
----------- ----------- ----------- -----------
1 3 5 6
2 5 6 6
3 7 6 2
4 7 6 9
5 2 1 8
6 3 2 8
(6 row(s) affected)
