Finding the last Non-Null value in the aggregate query

Question

I have the following query:

SELECT * FROM ( SELECT unix_timestamp, input_raw, tag_id from [200030].[dbo].inputs WHERE inputs.date_time > dateadd(day,-1,getdate()) AND (tag_id = 92164 or tag_id = 92149) ) src pivot ( max(input_raw) FOR tag_id IN ([92164], [92149]) ) piv ORDER by unix_timestamp DESC 

which is great and works. This gives me the results:

However, I would like the request to do one more thing for me.

Whenever there is a “NULL” result, I would like the query to replace “NULL” with the last “non-NULL” value in the column.

For example, the first NULL specified in column "92164" will be replaced with "211".

In addition, it is possible that there will be a few “NULL I row”, so the query will have to keep going up the column until it finds NULL.

I was able to accomplish this with php. Putting the results in a 2D associative array and running a function that finds zeros, then iterates over to find the last one is not NULL, but I really want to do all this in SQL, if possible. I would rather use

 while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){ //code } 

than assigning multiple arrays.

Any help?

thanks

// EDIT

I forgot to add that this is applicable only if there are non-zero values ​​that exceed the zero value. For example, it is valid if the first line is NULL.

Answer 1

You can put your summary request in the CTE and reuse the CTE when you figure out which value to show. You only have null values ​​in the first column, but here is a version that deals with zeros in both columns.

 WITH C AS ( SELECT * FROM ( SELECT unix_timestamp, input_raw, tag_id FROM inputs WHERE date_time > dateadd(day,-1,getdate()) AND (tag_id = 92164 OR tag_id = 92149) ) src PIVOT ( MAX(input_raw) FOR tag_id IN ([92164], [92149]) ) piv ) SELECT C1.unix_timestamp, ( SELECT TOP(1) C2.[92164] FROM C AS C2 WHERE C1.unix_timestamp <= C2.unix_timestamp AND C2.[92164] IS NOT NULL ORDER BY C2.unix_timestamp ) AS [92164], ( SELECT TOP(1) C2.[92149] FROM C AS C2 WHERE C1.unix_timestamp <= C2.unix_timestamp AND C2.[92149] IS NOT NULL ORDER BY C2.unix_timestamp ) AS [92149] FROM C AS C1 ORDER by C1.unix_timestamp DESC; 

If you have a performance problem, it might be faster for you to save the result from the CTE in a temp table with a useful clustered key index.

 CREATE TABLE #C ( unix_timestamp int PRIMARY KEY, [92164] int, [92149] int ); INSERT INTO #C SELECT * FROM ( SELECT unix_timestamp, input_raw, tag_id FROM inputs WHERE date_time > dateadd(day,-1,getdate()) AND (tag_id = 92164 OR tag_id = 92149) ) src PIVOT ( MAX(input_raw) FOR tag_id IN ([92164], [92149]) ) piv; SELECT C1.unix_timestamp, ( SELECT TOP(1) C2.[92164] FROM #C AS C2 WHERE C1.unix_timestamp <= C2.unix_timestamp AND C2.[92164] IS NOT NULL ORDER BY C2.unix_timestamp ) AS [92164], ( SELECT TOP(1) C2.[92149] FROM #C AS C2 WHERE C1.unix_timestamp <= C2.unix_timestamp AND C2.[92149] IS NOT NULL ORDER BY C2.unix_timestamp ) AS [92149] FROM #C AS C1 ORDER by C1.unix_timestamp DESC; DROP TABLE #C; 

Answer 2

You can use the adaptation of the calculation method "total amount" in more detail here . Create a temporary table or table variable to hold the results of your pivot query, with an extra column to hold the last non-zero value of column 92164. I cannot force SQLFiddle to do this, but it works on my machine (famous last words)

 CREATE TABLE #pivot_results ([unix_timestamp] int, [92164] int, [92149] int) INSERT INTO #pivot_results ([unix_timestamp], [92164], [92149]) VALUES (1361893407, NULL, 294), (1361893218, 207, 294), (1361893108, NULL, 292), (1361892807, 211, 292), (1361892799, NULL, 292) CREATE TABLE #update_me ([unix_timestamp] int, [92164] int, [92149] int, last_not_null_92164 int) DECLARE @last_not_null_92164 INT = NULL; INSERT INTO #update_me([unix_timestamp], [92164], [92149], last_not_null_92164) SELECT unix_timestamp, [92164], [92149], NULL FROM #pivot_results ORDER BY unix_timestamp DESC UPDATE #update_me SET @last_not_null_92164 = last_not_null_92164 = ISNULL([92164],@last_not_null_92164) FROM #update_me SELECT unix_timestamp ,last_not_null_92164 AS [92164] ,[92149] FROM #update_me ORDER BY unix_timestamp DESC