Filling [propagating] empty values with last nonNull value using T-SQL

So you have NULL values in your SQL Server table and you want to populate those NULL values with the last non-NULL value, based on a particular order. Once you have only one NULL values encapsulated between two populated values, there are quick and fast solutions. But what if you find a larger gap of NULL values and you want to populate these values as well?

Let’s make a small example to visualize the problem. Connect to your favorite SQL Server database and fun this:

DROP TABLE IF EXISTS testNonNull;
GO

CREATE TABLE testNonNull
(id INT IDENTITY(1,1) NOT NULL
,valFluct TINYINT
,valIncrem TINYINT
,dateD SMALLDATETIME);
GO

INSERT INTO testNonNull (valFluct,valIncrem, dateD)
SELECT 10,10, '2018/08/01'
UNION ALL SELECT 14,14, '2018/08/02'
UNION ALL SELECT NULL,NULL, '2018/08/03'
UNION ALL SELECT 9,16, '2018/08/04'
UNION ALL SELECT NULL,NULL, '2018/08/05'
UNION ALL SELECT NULL,NULL, '2018/08/06'
UNION ALL SELECT NULL,NULL, '2018/08/07'
UNION ALL SELECT 32,18, '2018/08/08'
UNION ALL SELECT 45,22, '2018/08/09'
UNION ALL SELECT NULL,NULL, '2018/08/10'
UNION ALL SELECT NULL,NULL, '2018/08/11'
UNION ALL SELECT 14,25, '2018/08/12'
UNION ALL SELECT NULL,NULL, '2018/08/13';
GO

And once you run a simple SELECT statement against this testNonNULL table, you will see the gaps:

2018-08-05 19_22_19-SQLQuery2.sql - TOMAZK_MSSQLSERVER2017.test (TOMAZK_Tomaz (60))_ - Microsoft SQL

A simple solution using OVER() clause for the ROWS UNBOUNDED PRECEDING would be a easy solution, but once applied, you quickly realize, that it is working correctly only for incremental values, eg.:

SELECT
dateD 
,valFluct AS FluctuatingValue
,MAX(valFluct) OVER 
  (ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_FluctuatingValues
,valIncrem
,MAX(valIncrem) OVER 
   (ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_IncrementalValues
FROM testNonNull
ORDER BY dateD

All works fine for the Incremental values, where as the Fluctuating values are not populated correctly:

2018-08-05 19_27_50-SQLQuery2.sql - TOMAZK_MSSQLSERVER2017.test (TOMAZK_Tomaz (60))_ - Microsoft SQL

So the query returns wrong values for the FluctuatingValue column two times, every time the lower number of currently maximum running number appears – and this is absolutely not correct. On the other hand, Incremental values (since they increment with every new value), are populated correctly.

In order to keep the simplicity of the window function and not overdo with complicated T-SQL, the following solution is genius, yet intriguing. Converting the value and column – upon which the sort is being done – to binary to extract the max value for the current window, will satisfy both the incremental and fluctuating type of values. The original post was introduced by Itzik Ben-Gan in SQL Magazine.

Simple substitution will do the trick:

SELECT
dateD 
,valFluct AS FluctuatingValue
,CAST(SUBSTRING(MAX(CAST(dateD AS BINARY(32)) + 
     CAST(valFluct AS BINARY(12))) 
     OVER( ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING ),33,12) AS INT) 
         AS nonNULL_FluctuatingValues
,valIncrem
,MAX(valIncrem) OVER 
 (ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_IncrementalValues
FROM testNonNull
ORDER BY dateD

And the return results are what you would want for the values to be filled in with.

2018-08-05 19_41_59-SQLQuery2.sql - TOMAZK_MSSQLSERVER2017.test (TOMAZK_Tomaz (60))_ - Microsoft SQL

Enjoy!

 

Tagged with: , , , , ,
Posted in Uncategorized
4 comments on “Filling [propagating] empty values with last nonNull value using T-SQL
  1. Emil says:

    “Propagading” is not a word. “Propagating” is: https://www.merriam-webster.com/dictionary/propagate

    Like

  2. thanks for sharing. this helped me today 🙂

    Itzik is insane! My brain was mush after sitting through one of his full day training sessions.

    Liked by 1 person

  3. Tyler Phillis says:

    Hi, please forgive the newbe question, but this may help me help my programmer solve an almost identical problem.

    Will this solution work with MS SQL?

    Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Categories
Follow TomazTsql on WordPress.com
Programs I Use
Programs I Use
Programs I Use
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

€2.00

Top SQL Server Bloggers 2018
TomazTsql

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Search Msdn

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

R-bloggers

Tomaz doing BI and DEV with SQL Server and R, Python and beyond

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by hundreds of R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and DLM in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

%d bloggers like this: