Create a query to fill the gaps in a 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 a sensor, something like this:

As you can see the value always increments.

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

Is this something doable with just a query?

Thanks in advance.

Is it correct to assume that you want to do `1.` fill 0 with pre-post 2 record average when pre-post 2 records aren't zeros? `2`. Otherwise (=if there are consecutive zeros) fill it with the pre-record value? — bonCodigo, Feb 26 '13 at 11:11
@bonCodigo: right. All consecutive zeros should be treated like one gap putting on all of them the same value (the average between the last available value and the following one). I'll edit the question to make it clearer. — Ignacio Soler Garcia, Feb 26 '13 at 14:36

Tim Schmelter · Answer 1 · 2013-02-26T14:49:32.550

Maybe not the most efficient one, 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 a CTE to get consecutive numbers ordered by Creation Date. Then this number is used to get the new data according to its previous and next value.

Here's a demo with similar schema(i have used an int instead of the datetime):

Update

Nice one but it does not handle gaps with multiple 0

Good catch, here's the modified sql which takes that 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 consecutive zeros on 5,6)

Ok, can it be modified to avoid putting nulls on 0s on the first positions? (with insert into Data Values(1,0)) — Ignacio Soler Garcia, Feb 26 '13 at 14:55
Another one (forgot me if I'm boring). Can the update be modified to fill 0s on the last positions to include the last value available (insert into Data Values(10,2555) insert into Data Values(11,0)). 11 Should end with 2555 value — Ignacio Soler Garcia, Feb 26 '13 at 14:57

score 1 · Answer 2 · answered Feb 26 '13 at 11:24

1

I've got another variation:

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

answered Feb 26 '13 at 11:24

Neil Mussett

710
6
8

Oh, and I tested it out on a very cool website, SQL Fiddle: http://sqlfiddle.com/#!2/56f28/15 – Neil Mussett Feb 26 '13 at 11:30
This one is the only one I found to handle all cases like start & end with 0, multiple 0, etc but it does not include the update clause. How would it be? – Ignacio Soler Garcia Feb 26 '13 at 14:49
Just select the 'AvgValue' and use the whole sql as a subquery expression in your UPDATE query. See: http://stackoverflow.com/questions/2586517/subqueries-in-update-set-sql-server-2005 – Neil Mussett Feb 26 '13 at 16:46

score 0 · Answer 3 · answered Feb 26 '13 at 11:39

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

Also please note that I am not sure if this method has any issues (other than updating all records) but showing merely 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

Fiddle demo

|          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 |

@SoMoS, sure it is your preference, if you get the average increase of the entire collection, you could add it as well. That would be more accurate than getting the average of nearest two numbers. — Kaf, Feb 26 '13 at 14:44

Aleksandr Fedorenko · Answer 4 · 2013-02-26T19:37:17.397

One more 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

Demo on SQLFiddle

@SoMoS Try my new answers;) – Aleksandr Fedorenko Feb 26 '13 at 19:32 — Aleksandr Fedorenko, Feb 26 '13 at 19:32

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

4 Answers4