missing data

Last Post 11 May 2011 10:54 AM by russellb. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages Not Resolved
dbandee
New Member
New Member

--
05 May 2011 11:52 AM
i am trying to solve this by not using cursor due to the number of records
i have table like this

SeqNum, COL1, COL2, DateStamp
1           , 22    ,  5       , 2010-01-01
2           , 22    ,  NULL , 2010-02-01
3           , 22    ,  NULL , 2010-03-01
4           , 22    ,  8       , 2010-04-01
5           , 22    ,  NULL , 2010-05-01
6           , 22    ,  NULL , 2010-06-01
7           , 22    ,  14     , 2010-07-01
8           , 22    ,  NULL , 2010-08-01
9           , 22    ,  19     , 2010-09-01

here is what i need, if value in COL 2 is missing it should be what is it in a previous available value (everything is by datestamp)

so COL2 -SeqNum 2 and 3 should be 5
so COL2 -SeqNum 5 and 6 should be 8
so COL2 -SeqNum 8 should be 14 and so on, and it goes for mils of records like this

you thoughts and suggestions are appreciated
russellb
New Member
New Member

--
05 May 2011 07:26 PM
WHILE EXISTS (select 1 from t1 where col2 is null)
begin
update t1
set col2 = t2.col2
from t1
join t1 t2
on t2.seqnum = t1.seqnum -1
where t1.col2 is null
end
dbandee
New Member
New Member

--
11 May 2011 10:45 AM
THANK YOU!
russellb
New Member
New Member

--
11 May 2011 10:54 AM
Welcome
You are not authorized to post a reply.

Acceptable Use Policy