Fill in the Blank

I’ve climbed Rainier a couple of times and there were a few crevasses we had to cross. Some were just a mere jump, others were wider and had to be crossed with a ladder.  Got me thinking about crevasses in data and how you might want to fill in those crevasses with the last known value until you cross to the other side and have data again.

Here’s a quick T-SQL tip to fill those crevasses:

Here’s your table:

select 1 as ID, ‘a’ as Col1 into #tmp1
union select 2, ‘b’
union select 3, NULL
union select 4, ‘d’
union select 5, NULL
union select 6, null
union select 7, ‘g’

ID	Col1
1	a
2	b
3	NULL
4	d
5	NULL
6	NULL
7	g

Let’s say you want a new field where the NULLs will be replaced w/ the last known NULL value. So in this case, you want ID = 3 to have value of ‘b’ and IDs 5 and 6 to have value of ‘d’.

SELECT t1.ID
,t1.Col1
,MAX(t1.Col1) OVER (ORDER BY t1.ID ROWS UNBOUNDED PRECEDING) AS FillInTheBlank
FROM #tmp1 t1

ID	Col1	FillInTheBlank
1	a	a
2	b	b
3	NULL	b
4	d	d
5	NULL	d
6	NULL	d
7	g	g

Now go fill those crevasses in your data!