Replacing NULLS

Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

JeffreyI see a cursor in your future (ducks and hides)...

Seriously, the only way the I know of to transpose one row of data onto another in the type of scenario you describe is to do it with a cursor:

-- Declare Local variables (one for each column, plus an
-- additional variable for each column to store the previous value)
DECLARE
@.TimeStamp datetime,
@.System201 int,
@.System500 int,
@.System345 int,
...
@.System201_Old int,
@.System500_Old int,
@.System345_Old int,
...
@.Counter int

-- Declare the cursor
DECLARE rolling_cursor CURSOR
FROM SELECT * FROM TABLE1 ORDER BY TimeStamp

-- Open the cursor
OPEN rolling_cursor

-- Set an initial counter
Set @.Counter = 1

-- Fetch the first row of values into the local variables
FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345

-- WHILE your way through the table, updating the next row
-- with the previous rows data when the next row's columns
-- are null (see COALESCE)
WHILE fetch_status <> -1
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
IF @.Counter = 1
BEGIN
@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
END
ELSE
BEGIN
UPDATE TABLE1 SET
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
...
WHERE TimeStamp = @.TimeStamp

@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
@.Counter = @.Counter + 1
END

FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345
END

-- Close and deallocate the cursor
CLOSE rolling_cursor
DEALLOCATE rolling_cursor

-- Print a message to the user to show how many records were updated
Print @.Counter + ' records updated.'

Notes:
1. Not tested
2. Not guaranteed
3. Mileage may vary
4. Please don't sue me
5. Don't sic the DBA gods on me for suggesting a cursor
6. I am assuming that the PK on this table is TimeStamp (and that Timestamp is sequential). This solution does not work otherwise.

Best of luck,

Hugh Scott
Originally posted by JeffreyELewis
Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

Jeffrey|||Jeez, i spend all that time formatting the script with spaces and everything (to show nesting) and it comes out looking out looking like that!?!?!

First, I humbly apologize if it is difficult to read through. Second, it anyone knows how to make spaces or tabs work on this forum, I would be grateful.

Regards,

Hugh Scott|||keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||I appreciate your amendments. You're right on the comment about timestamps -- I missed that in the original statement of requirements.

Oh well, back to the drawing board!!!

Regards,

Hugh Scott

Originally posted by Paul Young
keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||I assumed that the PK was Timestamp. I inferred that the poster made an error and meant to put /03 instead of /02. I know what happens when you assume...

Kindest regards,

Hugh Scott

Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||Your right. That was a typo. The year should have been 03 instead of 02 and the PK is the [timestamp] field.|||it looks like hmscott provided the answer. Post back and let us know if it worked.|||Now, when your information is clear, I can post my solution.

/*
Script drops Table1, uses transaction to support 2 solutions in one script
*/

--INIT
drop table Table1
GO
create table Table1(
[timestamp] datetime primary key clustered
,[System201] int null
,[System500] int null
,[System345] int null
)
GO
set dateformat MDY
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/1/03', 1, 2, 4)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/2/03', 3,NULL,NULL)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/3/03',NULL, 5, 8)
GO

begin tran
select * from Table1

--SOLUTION WITHOUT CURSOR OR PSEDOCURSOR
/*
Recomended use of clustered PK on [timestamp] and separate index on each (System...) column for large table
*/
update t1 set
t1.[System201]=
(
select t2.[System201]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System201] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System201] is null
update t1 set
t1.[System500]=
(
select t2.[System500]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System500] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System500] is null
update t1 set
t1.[System345]=
(
select t2.[System345]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System345] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System345] is null

select * from Table1
rollback tran
begin tran
select * from Table1

--PSEDOCURSOR SOLUTION
declare @.timestamp datetime
declare @.System201 int
declare @.System500 int
declare @.System345 int
declare @.timestampPre datetime
declare @.System201Pre int
declare @.System500Pre int
declare @.System345Pre int
set nocount on
select @.timestampPre=[timestamp],@.System201Pre=[System201],@.System500Pre=[System500],@.System345Pre=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2)
while 1=1 begin
select @.timestamp=[timestamp],@.System201=[System201],@.System500=[System500],@.System345=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2 where t2.[timestamp]>@.timestampPre)
if @.@.rowcount=0 break
set @.System201Pre=isnull(@.System201,@.System201Pre)
set @.System500Pre=isnull(@.System500,@.System500Pre)
set @.System345Pre=isnull(@.System345,@.System345Pre)
if @.System201 is null or @.System500 is null or @.System345 is null
update Table1 set
[System201]=@.System201Pre
,[System500]=@.System500Pre
,[System345]=@.System345Pre
where [timestamp]=@.timestamp
set @.timestampPre=@.timestamp
end
set nocount off

select * from Table1
rollback tran|||Forgive me for being simple but there is a quick and dirty way to update previous record with data from next record.

Select *, identity(int ,1,1) as seq
into #temp
from "your table"
order by timestamp

update t1
set t1.System201 = t2.System201
from #temp t1 inner join #temp t2 on
t1.seq = t2.seq - 1|||Yes, I call that algorithm "ordered streams". But I was trying to solve
situation like this

1 1
2 NULL
3 NULL
->
1 1
2 1
3 1

by query without cursor.|||Why not use a subquery?

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld )

I'm suppose it isn't the finnest but I'm shure it's cheaper than cursors, no?|||Cesar, I do not uderstand what you mean by your code.

It can be solved by single quary

select t.keyfld,t.valfld,t1.keyfld,t1.valfld
from tblnam t
join tblnam t1 on t1.keyfld=(select max(t2.keyfld) from tblnam t2 where t2.keyfld<=t.keyfld and t2.valfld is not null)

But this query is not scalable (N^3), cursor is better algorithm.|||I don't know exactly how to calculate the difference between subqueries and joins, focusing in througput, because I used to work only with lower than 100,000 records tables, so my point is I agree about cursor structures and algorithms in theoretical terms, but not in speed, at least in MSSQL7 implementation.
So I'm always preffer to complicate my scripts with more sophisticated selects/joins/temporary tables/indexes, etc...
The only drawback I've found is maintenance, because almost any VB programmer can deal with cursors but complex SQL statements.
So in my record's range its a question of maintenance/performance for me and I've choseen performance until now, against cursors.
What do you think?|||I already compared cursor and noncursor solutions at this forum.
Problem solvable without cursor can be faster without cursor always.

1. I just say, that single query solution of this problem is slower than simple cursor solution.
2. I do not understand your code

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld
)|||Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?

I just tried to insert a non-cursor sample about finding previous record.