Query to replace NULLS with value from previous record
-
Using a view in SQL2K, I was able to join two tables via a left outer join to get this far. Now, my problem is how to fill the NULL's with the previous value. The data in table TimeStamp is logged every second, while the table with the RC is logged infrequently, but I want to create a query to join the two and assume the previous RC.
Here's where I am....
('8:23:00','GOOD')
('8:23:01','GOOD')
('8:23:02','GOOD')
('8:23:03',<NULL>)
('8:23:04',<NULL>)
('8:23:05',<NULL>)
('8:23:06','RC02')
('8:23:07','RC02')
('8:23:08','RC02')
('8:23:09','RC03')
('8:23:10','RC03')
('8:23:11',<NULL>)
('8:23:12',<NULL>)
('8:23:13','RC04')
('8:23:14','RC04')
('8:23:15','RC04')
Here's where I want to get to....
('8:23:00','GOOD')
('8:23:01','GOOD')
('8:23:02','GOOD')
('8:23:03','GOOD')
('8:23:04','GOOD')
('8:23:05','GOOD')
('8:23:06','RC02')
('8:23:07','RC02')
('8:23:08','RC02')
('8:23:09','RC03')
('8:23:10','RC03')
('8:23:11','RC03')
('8:23:12','RC03')
('8:23:13','RC04')
('8:23:14','RC04')
('8:23:15','RC04')
Any help would be appreciated!
-
Select within a select.
There's not much info here to work with but this should get you started.
Select T1.Col1, ISNULL(T1.Col2,
(Select T2.col2 from YourResultSet T2 where T2.Clol1 = (Select max(T3.Col1) from YourResultSet T3 where T3.Col1 < T3.Col1 and T3.Col2 IS NOT NULL)
)
)
from YourResultSet T1
Or something like that.
Oh, This requires Col1 to be of datetime data type.
-
I tried this on the sample data above. It worked but it took over 90 seconds to process a table with only 16 records and two columns. Is there a cleaner or more efficient way?
-
Post the DDL of the tables you have, and some sample rows of data.
Then we have someting to work with, and maybe something can be done.
/Kenneth
-
And what should happen when all rows contain <NULL>?
To get what you want, every row has to link with all previous rows to find the first non <NULL> entry. And I don't think you can stop the linking process once a non <NULL> value is found. Although 90 seconds for 16 rows does look a bit suspicious.
-
If I understand it right, you have 2 tables - one has a row for each second, one has 1 row every few seconds. If there is no row in the second table, you want to take value from the last earlier existing row. NULLs are not present in the tables and were created just in the view by using LEFT JOIN on time column.
Well, what about not creating the intermediary view at all and go like this:
SELECT s.timecol, (select TOP 1 c.status from tbl_CR c where c.timecol <= s.timecol order by c.timecol desc)
FROM tbl_stamp s
I'm afraid the performance will not be too good on large tables, but maybe it wouldn't be too bad... at least worth a try. Anyway, the only other possibility I can see would be to join both tables ON c.timecol<=s.timecol and filter the result, which also doesn't look like something great for performance. But it is Friday evening and I may be just tired or dumb and not see the proper solution...
-
This should be faster...
Select Max(Col1) as Col1, Col2
INTO #Temp
From YourResultSet
Where Col2 IS NOT NULL
Group By Col2
Select T1.Col1, ISNULL(T1.Col2,
(
Select TOP 1 T2.col2 from #Temp T2
where T2.Clol1 < T1.Col1
Order by T2.Col1 DESC
)
)
from YourResultSet T1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply