Create a query to fill in the gaps in the table due to bad data

Question

I have a table with the following schema:

DateTime [Creation Date] PK int [Data] 

The column data has values ​​coming from the sensor, something like this:

 123 225 354 578 0 2151 2331 0 2555 2678 

As you can see, the value is always increasing.

Due to a problem in the sensor, from time to time we get 0 between valid values. This creates several problems for us when we try to use the data, so we want to fill something with these 0 spaces. Ideally, we would like to set the average value between the previous and next value, if this is not possible, we want to repeat the previous value.

Is this something feasible only with the request?

Thanks in advance.

Answer 1

Perhaps not the most efficient, but should work:

 WITH cte AS (SELECT [Creation Date], Data, rn=Row_number() OVER(ORDER BY [Creation Date]) FROM dbo.Table) UPDATE cte SET Data = ( ( (SELECT c2.Data FROM cte c2 WHERE c2.rn = cte.rn - 1) + (SELECT c2.Data FROM cte c2 WHERE c2.rn = cte.rn + 1) ) / 2 ) WHERE Data = 0; 

I use Row_Number in CTE to get sequential numbers sorted by Creation Date . Then this number is used to obtain new data in accordance with their previous and next value.

Here is a demo with a similar scheme (I used int instead of datetime )

Refresh

Nice, but it doesn't handle spaces with multiple 0

Good catch, here is a modified sql that takes this into account:

 WITH cte AS (SELECT [Creation Date], Data, rn=Row_number() OVER(ORDER BY [Creation Date]) FROM dbo.Table) UPDATE cte SET Data = ( ( (SELECT c2.Data FROM cte c2 WHERE c2.rn = (SELECT MAX(RN)FROM CTE c3 WHERE c3.RN<cte.RN AND c3.Data<>0)) + (SELECT c2.Data FROM cte c2 WHERE c2.rn = (SELECT MIN(RN)FROM CTE c3 WHERE c3.RN>cte.RN AND c3.Data<>0))) / 2 ) WHERE Data = 0; 

Demo (with 5.6 consecutive zeros)

Answer 2

I have another option:

 SELECT BadDate, T1.Data AS PrevData, T2.Data AS NextData, (T1.Data + T2.Data) / 2 AS AvgValue FROM ( SELECT T1.CreationDate As BadDate, Max(T2.CreationDate) As PrevDate, Min(T3.CreationDate) As NextDate FROM TestData T1, TestData T2, TestData T3 WHERE T1.Data = 0 AND T2.Data <> 0 AND T2.CreationDate < T1.CreationDate AND T3.Data <> 0 AND T3.CreationDate > T1.CreationDate GROUP BY T1.CreationDate ) DateData INNER JOIN TestData T1 ON DateData.PrevDate = T1.CreationDate INNER JOIN TestData T2 ON DateData.NextDate = T2.CreationDate 

Answer 3

If you're not worried about the average, this method can add a number to the previous value.

Also note that I'm not sure if this method has any problems (other than updating all records), but showing just as a different and simple approach ...

 declare @new int = 1 update mytable set @new = val = case when val = 0 then @new + 1 else val end 

Demo screenshot

 | D | VAL | --------------------- | 2013-01-01 | 123 | | 2013-01-02 | 225 | | 2013-01-03 | 354 | | 2013-01-04 | 578 | | 2013-01-05 | 579 |--Updated | 2013-01-06 | 2151 | | 2013-01-07 | 2331 | | 2013-01-08 | 2332 |--Updated | 2013-01-09 | 2555 | | 2013-01-10 | 2678 | 

Answer 4

Another option

 UPDATE s SET s.Data = (COALESCE(o1.Data, o2.Data) + COALESCE(o2.Data, o1.Data)) / 2 FROM dbo.sensor s OUTER APPLY ( SELECT TOP 1 s2.Data AS Data FROM dbo.sensor s2 WHERE s2.Data != 0 AND s.[Creation Date] < s2.[Creation Date] ORDER BY s2.[Creation Date] ASC ) o1 OUTER APPLY ( SELECT TOP 1 s3.Data AS Data FROM dbo.sensor s3 WHERE s3.Data != 0 AND s.[Creation Date] > s3.[Creation Date] ORDER BY s3.[Creation Date] DESC ) o2 WHERE s.Data = 0 

SQLFiddle Demo