A T-SQL Sample Query

Fill Null Value with T-SQL.sql

Create Table test
( year int, month int,CustomerID int, AccountNum int, Amount int)
GO
Insert test 
values 
(2019,11,14,1,200),
(2019,11,20,1,500),
(2019,11,20,2,25),
(2019,12,20,1,670),
(2019,12,20,2,50),
(2020,1,20,2,700),
(2020,2,20,2,746),
(2020,3,20,1,550),
(2020,3,20,2,600),
(2020,4,20,2,720)  ;
;with mycte as ( 
select  year,month,CustomerID,AccountNum
  from  (values(2019),(2020)) a(Year)
  , (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) b(month),
 (select distinct CustomerID from test) c,
 (select distinct AccountNum from test) d 
 )
 
 ,mycte2 as (select 
  isnull(t.year,m.year) year,
  isnull(t.month,m.month) month,
 isnull(t.CustomerID,m.CustomerID) CustomerID,
   isnull(t.AccountNum,m.AccountNum) AccountNum
   , Amount
  from mycte m left join test t on   
 m.year=t.year and m.month=t.month 
 and m.CustomerID=t.CustomerID and m.AccountNum=t.AccountNum
 )


 select m.year,m.month,m.CustomerID,m.AccountNum, isnull(m.Amount, t1.Amount) Amount
 from mycte2 m OUTER APPLY (select top (1) Amount from mycte2 t 
 where m.CustomerID=t.CustomerID and m.AccountNum=t.AccountNum
 and datefromparts(m.year,m.month,1)> datefromparts(t.year,t.month,1)
 and t.Amount IS NOT NULL
ORDER BY datefromparts(t.year,t.month,1) desc ) t1
WHERE  isnull(m.Amount, t1.Amount) is not null
and datefromparts(m.year,m.month,1)<= (select Max(datefromparts(year,month,1)) from test)

 order by  Year, month,CustomerID, AccountNum

  

 Drop Table test
 /*
 year	month	CustomerID	AccountNum	Amount
2019	11	14	1	200
2019	11	20	1	500
2019	11	20	2	25
2019	12	14	1	200
2019	12	20	1	670
2019	12	20	2	50
2020	1	14	1	200
2020	1	20	1	670
2020	1	20	2	700
2020	2	14	1	200
2020	2	20	1	670
2020	2	20	2	746
2020	3	14	1	200
2020	3	20	1	550
2020	3	20	2	600
2020	4	14	1	200
2020	4	20	1	550
2020	4	20	2	720
 */