Replacing null values with the previous non-null value in each group

Question

I'm connecting to Microsoft SQL Server on Tableau through a custom SQL query. I have a table with 3 fields DateTime, TagName, Value, and I want to replace null values in the Value field by the last (respecting the DateTime value) non-null value in each group of TagName.

|---------------------|------------------|-----------------|
|     DateTime        |     TagName      |      Value
|---------------------|------------------|-----------------
|  15.04.2019 16:51:30|         A        |       10
|---------------------|------------------|----------------- 
|  15.04.2019 16:52:42|         A        |       NULL
|---------------------|------------------|----------------- 
|  15.04.2019 16:53:14|         A        |       NULL
|---------------------|------------------|----------------- 
|  15.04.2019 17:52:14|         A        |       15
|---------------------|------------------|----------------- 
|  15.04.2019 16:51:30|         B        |       NULL
|---------------------|------------------|----------------- 
|  15.04.2019 16:52:42|         B        |       NULL
|---------------------|------------------|-----------------
|  15.04.2019 16:53:14|         B        |       NULL
|---------------------|------------------|----------------- 
|  15.04.2019 17:52:14|         B        |       15
|---------------------|------------------|-----------------|

The new table should look like this:

|---------------------|------------------|-----------------|
|     DateTime        |     Computer     |      Value
|---------------------|------------------|-----------------
|  15.04.2019 16:51:30|         A        |       10
|---------------------|------------------|----------------- 
|  15.04.2019 16:52:42|         A        |       10
|---------------------|------------------|----------------- 
|  15.04.2019 16:53:14|         A        |       10
|---------------------|------------------|----------------- 
|  15.04.2019 17:52:14|         A        |       15
|---------------------|------------------|----------------- 
|  15.04.2019 16:51:30|         B        |       0
|---------------------|------------------|----------------- 
|  15.04.2019 16:52:42|         B        |       0
|---------------------|------------------|-----------------
|  15.04.2019 16:53:14|         B        |       0
|---------------------|------------------|----------------- 
|  15.04.2019 17:52:14|         B        |       15
|---------------------|------------------|-----------------|

This is already what I've tried, but it replaces NULL values without considering the TagNames values (In this example there is only one TagName).

SELECT  Computer, DateTime
,       CASE 
        WHEN Value IS NULL 
        THEN                                
       (SELECT TOP 1 Value 
        FROM History 
        WHERE DateTime<T.DateTime 
              AND TagName='RM02EL00CPT81.rEp'
              AND DateTime >='2018-12-31 23:59:00' 
              AND wwRetrievalMode='Delta'
              AND Value IS NOT NULL ORDER BY DateTime DESC
       ) 
        ELSE Value 
        END 
        AS ValueNEW
FROM History T
WHERE  TagName='RM02EL00CPT81.rEp' AND DateTime >='2018-12-31 23:59:00' AND wwRetrievalMode='Delta'

I wanted to do almost the same thing by adding OVER(PARTITION BY TagName), but it threw an error. (This is because it doesn't work with SELECT TOP 1.)

score 3 · Accepted Answer · answered Apr 17 '19 at 08:19

This is a "classic" Gaps and Islands question. You can achieve this without a 2 scans, or a triangular join by using the window functions:

WITH VTE AS(
    SELECT CONVERT(datetime, [DateTime],104) AS [DateTime],
           TagName,
           [Value]
    FROM (VALUES ('15.04.2019 16:51:30','A',10  ),
                 ('15.04.2019 16:52:42','A',NULL),
                 ('15.04.2019 16:53:14','A',NULL),
                 ('15.04.2019 17:52:14','A',15  ),
                 ('15.04.2019 16:51:30','B',NULL),
                 ('15.04.2019 16:52:42','B',NULL),
                 ('15.04.2019 16:53:14','B',NULL),
                 ('15.04.2019 17:52:14','B',15  )) V([DateTime],TagName,[Value])),
Grps AS(
    SELECT [DateTime],
           TagName,
           [Value],
           COUNT(CASE WHEN [Value] IS NOT NULL THEN 1 END) OVER (PARTITION BY TagName ORDER BY [DateTime]
                                                                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Grp
    FROM VTE)
SELECT DateTime,
       TagName,
       ISNULL(MAX([Value]) OVER (PARTITION BY TagName, Grp),0) AS [Value]
FROM Grps
ORDER BY TagName, [DateTime]

This resolved my problem and I'd never thought like this. Thank you very much for your help @Larnu — sgokce, Apr 17 '19 at 09:25
You're welcome @sgokce . The Window Function bring some powerful/useful capabilities to SQL Server, so it's worth while learning about them. :) — Thom A, Apr 17 '19 at 09:26

score 1 · Answer 2 · answered Apr 17 '19 at 08:18

Try this

;WITH CTE([DateTime],TagName,Valu)
AS
(
SELECT '15.04.2019 16:51:30','A' , 10    UNION ALL
SELECT '15.04.2019 16:52:42','A' , NULL  UNION ALL
SELECT '15.04.2019 16:53:14','A' , NULL  UNION ALL
SELECT '15.04.2019 17:52:14','A' , 15    UNION ALL
SELECT '15.04.2019 16:51:30','B' , NULL  UNION ALL
SELECT '15.04.2019 16:52:42','B' , NULL  UNION ALL
SELECT '15.04.2019 16:53:14','B' , NULL  UNION ALL
SELECT '15.04.2019 17:52:14','B' , 15
)
SELECT [DateTime],TagName As Computer,
        ISNULL(CASE WHEN Valu IS NOT NULL   
            THEN Valu
            ELSE 
                (
                SELECT TOP 1 Valu FROM  
                CTE i
                WHERE i.TagName = o.TagName     
                ) END,0) As Valu
FROM CTE o

Result

DateTime                Computer    Valu
---------------------------------------------
15.04.2019 16:51:30     A           10
15.04.2019 16:52:42     A           10
15.04.2019 16:53:14     A           10
15.04.2019 17:52:14     A           15
15.04.2019 16:51:30     B           0
15.04.2019 16:52:42     B           0
15.04.2019 16:53:14     B           0
15.04.2019 17:52:14     B           15

score 0 · Answer 3 · answered Apr 17 '19 at 09:43

0

So you're trying to retrieve data from Wonderware Historian. Perhaps you don't need any windowing and replacing, because the Historian retrieval engine should be able to give you the data you need without nulls. Try this:

select DateTime, TagName as Computer, Value
from History
where TagName in ('A', 'B') --put here the tagnames you want to retrieve
and DateTime > '2018-12-31'
AND wwRetrievalMode='Delta'
order by TagName, DateTime

answered Apr 17 '19 at 09:43

PiotrS

180
3
16

Thank you for your answer but I don't understand what you mean. If you are mentioning the Delta retrieval mode, I'm already retrieving the data in Delta mode. As far as I know, in this mode, we could still get null values. At least this is what they say in the "Historian Concepts Guide" and also what I observe in my data. – sgokce Apr 17 '19 at 09:54
@sgokce Correct, it can return nulls but should not return multiple in a row; from historian user guide: "The initial NULL value after a non-NULL is always returned. Multiple NULL values are suppressed. The first non-NULL after a NULL is always returned even if it is the same as the previous non-NULL value." If you have nulls in the returned data, then probably that tag had bad quality at that time. I'd think you should only use good quality values for Tableau visualizations. So you could just filter out nulls by adding "and Value is not null" to the condition and use only good quality values – PiotrS Apr 17 '19 at 10:28
Thank you for your detailed answer. Filtering NULL values can cause gaps in the data and I'd like to avoid this kind of gaps in my analysis. I saw that we can also apply an "optimistic quality rule" (wwQualityRule) to avoid null values. The OPTIMISTIC setting for the quality rule lets you retrieve information that is possibly incomplete but may nevertheless provide better results in the counter and integral retrieval modes where the calculation cycle contains data gaps.This setting calculates using the last known good value prior to the gap(if possible) – sgokce Apr 18 '19 at 05:14

Replacing null values with the previous non-null value in each group

3 Answers3