Replace Null value with the last integer value from the same column

Transact-SQL
1

Hello All,
How can I replace Null value of ‘Score’ Column using the last integer value of the same column in the below data.
Here is how my data looks.
|PAT_ID|Score|Recorded Time |Age|
| 1 |NULL|2019:01:15:00:00:00| 16 |
| 1 |NULL|2019:01:15:02:00:00| 16 |
| 1 |17 |2019:01:15:03:00:00| 16 |
| 1 |Null |2019:01:15:04:30:00| 16 |
| 1 |12 |2019:01:15:06:00:00| 16 |
| 1 |null |2019:01:15:08:00:00| 16 |
| 1 |24 |2019:01:15:09:00:01| 16 |
| 1 |null |2019:01:15:10:00:02| 16 |
| 1 |null |2019:01:15:12:00:03| 16 |
| 1 |13 |2019:01:15:14:00:04| 16 |
| 2 |28 |2019:02:01:08:00:05| 43 |
| 2 |28 |2019:02:01:09:00:05| 43 |
| 2 |null |2019:02:01:10:00:05| 43 |
| 2 |null |2019:02:01:11:00:05| 43 |
| 2 |14 |2019:02:01:13:00:05|43 |
| 2 |null |2019:02:01:15:00:05| 43 |
| 2 |19 |2019:02:01:17:00:05| 43 |

I searched in the internet and try everything that's out there but nothing worked. I tried following
cast(substring(Max(cast(PAT_ID as BINARY(15))+ CAST(Scale AS BINARY(12))) OVER (ORDER BY PAT_ID ROWS UNBOUNDED PRECEDING),16,12) AS INT) AS NonNullScore
I try to use first_value, Last_value, Min, MAx with unbounded preceding but I didnot get the expected result. I really appreciate the help.

2

This might not be the best solution but it works:-

Click Here - Solution 
if object_id('tempdb..#test') is not null drop table #test

create table #test (PAT_ID int,Score float null ,[Recorded Time]  datetime,Age int)

insert into #test values
 (1,NULL,	'20190115 00:00:00',16)
,(1,NULL,	'20190115 02:00:00',16)
,(1,17,		'20190115 03:00:00',16)
,(1,Null,	'20190115 04:30:00',16)
,(1,12,		'20190115 06:00:00',16)
,(1,null,	'20190115 08:00:00',16)
,(1,24,		'20190115 09:00:01',16)
,(1,null,	'20190115 10:00:02',16)
,(1,null,	'20190115 12:00:03',16)
,(1,13,		'20190115 14:00:04',16)
,(2,28,		'20190201 08:00:05',43)
,(2,28,		'20190201 09:00:05',43)
,(2,null,	'20190201 10:00:05',43)
,(2,null,	'20190201 11:00:05',43)
,(2,14,		'20190201 13:00:05',43)
,(2,null,	'20190201 15:00:05',43)
,(2,19,		'20190201 17:00:05',43)

GO

--Your data
select * from #test


--solution

if object_id('tempdb..#temp') is not null drop table #temp

;with cte as (
					select * from #test
					where Score is null 
) ,cte2 as (
					select * from #test 
					where Score is not null 
),cte3 as ( 
					select 
					 a.PAT_ID
					,a.Score as Score_x	 
					,a.[Recorded Time] as Timex	
					,b.PAT_ID as Updt_ID 
					,b.[Recorded Time] as Updt_DTTM
					from cte2 a
					Left join cte b on a.PAT_ID = b.PAT_ID and a.[Recorded Time] > b.[Recorded Time]

) 
select top (1) with ties
ROW_NUMBER() over (partition by x.PAT_ID,x.score,x.[Recorded Time] order by y.Timex) rn
,x.PAT_ID
,coalesce(Score,Score_x) as Score
,[Recorded Time]
,Age
into #temp
from #test x
left join cte3 y on x.PAT_ID = y.PAT_ID and x.[Recorded Time] = y.Updt_DTTM and  x.[Recorded Time] < y.Timex
order by ROW_NUMBER() over (partition by x.PAT_ID,x.score,x.[Recorded Time] order by y.Timex)
GO

update a 
set a.Score = b.score
from #test a
left join #temp b on a.PAT_ID = b.PAT_ID and a.[Recorded Time] = b.[Recorded Time]
where a.Score is null 



select * from #test
3 
SELECT t1.PAT_ID, ISNULL(t1.Score, previous.Score) AS Score, t1.[Recorded Time], t1.Age
FROM #test t1
OUTER APPLY (
    SELECT TOP (1) Score
    FROM #test t2
    WHERE t1.Score IS NULL AND
        t2.PAT_ID = t2.PAT_ID AND
        t2.Score IS NOT NULL AND
        t2.[Recorded Time] < t1.[Recorded Time]
    ORDER BY t2.[Recorded Time] DESC
) AS previous
4

Thank You very much.

5

hi sabs

i know the solution has already been given by Scott Pletcher

i have come up with the solution in a different way
hope it helps :slight_smile: :slight_smile:

i used recursive cte and grouping and max over
recursive cte is not good for performance if you have large amount of data

drop create data .... 
drop table #sampledata 
go 

create table #sampledata
(
PAT_ID  int ,
Score int,
RecordedTime  datetime,
Age int
)
go 


insert into #sampledata select  1 ,NULL ,'2019-01-15 00:00:00', 16 
insert into #sampledata select  1 ,NULL ,'2019-01-15 02:00:00', 16 
insert into #sampledata select  1 ,17   ,'2019-01-15 03:00:00', 16 
insert into #sampledata select  1 ,Null ,'2019-01-15 04:30:00', 16 
insert into #sampledata select  1 ,12   ,'2019-01-15 06:00:00', 16 
insert into #sampledata select  1 ,null ,'2019-01-15 08:00:00', 16 
insert into #sampledata select  1 ,24   ,'2019-01-15 09:00:01', 16 
insert into #sampledata select  1 ,null ,'2019-01-15 10:00:02', 16 
insert into #sampledata select  1 ,null ,'2019-01-15 12:00:03', 16 
insert into #sampledata select  1 ,13   ,'2019-01-15 14:00:04', 16 
insert into #sampledata select  2 ,28   ,'2019-02-01 08:00:05', 43 
insert into #sampledata select  2 ,28   ,'2019-02-01 09:00:05', 43 
insert into #sampledata select  2 ,null ,'2019-02-01 10:00:05', 43 
insert into #sampledata select  2 ,null ,'2019-02-01 11:00:05', 43 
insert into #sampledata select  2 ,14   ,'2019-02-01 13:00:05', 43 
insert into #sampledata select  2 ,null ,'2019-02-01 15:00:05', 43 
insert into #sampledata select  2 ,19   ,'2019-02-01 17:00:05', 43 

select * from #sampledata
go
SQL .. in a different way 
  ;WITH rn_cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY recordedtime) AS RN, 
                * 
         FROM   #sampledata), 
     grp_cte 
     AS (SELECT 1 AS grp, 
                * 
         FROM   rn_cte 
         WHERE  rn = 1 
         UNION ALL 
         SELECT CASE 
                  WHEN a.score IS NOT NULL THEN b.grp + 1 
                  ELSE b.grp 
                END AS grp, 
                a.* 
         FROM   rn_cte a 
                JOIN grp_cte b 
                  ON a.rn = b.rn + 1) 
SELECT *, 
       Max(score) 
         OVER( 
           partition BY grp 
           ORDER BY recordedtime) 
FROM   grp_cte 

go

image

1 Like
6

Sorry I misunderstood @Sabs expected result

Rsa

1 Like
7

hi

i find understanding what people
"who put posts here"
want is the most difficult part

doing it I have plenty of experience to give some solution
:slight_smile: :slight_smile:

8

Thank you very much. My expected result is as shown in above screen shot. Is there a way to improve performance with CTE. The above query is only part of the SQL that I am working on and I have large number of records. I need that expected result for other calculations and My SQL has a lot of subquery too.

9

hi Sabs

my suggestion would be to break it down into small bits
use Temp Tables( #TempTables ) and Indexes
rather than all 1 big query and its slow

query1 into #temp1 ... create indexes
query2 join #temp1 into #temp2 ... create indexes
query3 join #temp2 into #temp3 ... create indexes
Final Output query #Temp3
-- this approach will be good for performance

hope it helps
:slight_smile: :slight_smile: :+1 :+1:

1 Like