Farward fill Null values

Transact-SQL
1

Hi All,

I have asked this question before, but some gentleman asked me to provide data as in below,
I couldn't edit previous question rising this topic again.

I have 2 tables with same headers, I need to Union them first.
then with Union output I would like to forward fill null Locations to last not null value.
also, I would like to extract only last 3 days data from it.

I have attached image below for more clarification and table data also attached. please check.
I have latest SSMS installed :slight_smile:
Many thanks in advance for your help :slight_smile:

sql query
sql query437×529 50.5 KB

Table 1:

CREATE TABLE mytable(
Date DATE NOT NULL PRIMARY KEY
,Name VARCHAR(6) NOT NULL
,qty INTEGER NOT NULL
,Location VARCHAR(8)
);
INSERT INTO mytable(Date,Name,qty,Location) VALUES ('01/01/2022','Apple',2,'1st row');
INSERT INTO mytable(Date,Name,qty,Location) VALUES ('01/01/2022','Orange',10,'2nd row');
INSERT INTO mytable(Date,Name,qty,Location) VALUES ('02/01/2022','Apple',5,NULL);
INSERT INTO mytable(Date,Name,qty,Location) VALUES ('02/01/2022','Kiwi',1,'10th row');

Table 2:

CREATE TABLE mytable2(
Date DATE NOT NULL PRIMARY KEY
,Name VARCHAR(6) NOT NULL
,qty INTEGER NOT NULL
,Location VARCHAR(8)
);
INSERT INTO mytable2(Date,Name,qty,Location) VALUES ('03/01/2022','Orange',9,NULL);
INSERT INTO mytable2(Date,Name,qty,Location) VALUES ('04/01/2022','Apple',10,'3rd row');
INSERT INTO mytable2(Date,Name,qty,Location) VALUES ('05/01/2022','Kiwi',4,NULL);
INSERT INTO mytable2(Date,Name,qty,Location) VALUES ('05/01/2022','Apple',2,'5th row');
INSERT INTO mytable2(Date,Name,qty,Location) VALUES ('06/01/2022','Apple',5,NULL);

2

Umm.... You did not even try this test data as it obviously fails the primary key constraint.

As a one off here is how I would define the test data using temp tables and dates in ISO format.
It would also be better to avoid reserved words for column names but I have left them as they were.

CREATE TABLE #t1
(
	[Date] date NOT NULL
	,[Name] varchar(20) NOT NULL
	,qty int NOT NULL
	,[Location] varchar(20) NULL
	,PRIMARY KEY ([Name], [Date])
);
INSERT INTO #t1
VALUES ('20220101','Apple',2,'1st row')
	,('20220101','Orange',10,'2nd row')
	,('20220102','Apple',5,NULL)
	,('20220102','Kiwi',1,'10th row');

CREATE TABLE #t2
(
	[Date] date NOT NULL
	,[Name] varchar(20) NOT NULL
	,qty int NOT NULL
	,[Location] varchar(20) NULL
	,PRIMARY KEY ([Name], [Date])
);
INSERT INTO #t2
VALUES ('20220103','Orange',9,NULL)
	,('20220104','Apple',10,'3rd row')
	,('20220105','Kiwi',4,NULL)
	,('20220105','Apple',2,'5th row')
	,('20220106','Apple',5,NULL);

select * from #t1;
select * from #t2;

So what exactly do you think is wrong with the outline solution in the previous thread?

Using this test rig:

WITH AllData
AS
(
	SELECT [Date], [Name], qty, [Location] FROM #t1
	UNION ALL
	SELECT [Date], [Name], qty, [Location] FROM #t2
)
SELECT [Date], [Name], qty
	,[Location] AS Original_Location
	,COALESCE([Location], LAG([Location]) OVER (PARTITION BY [Name] ORDER BY [Date])) AS New_Location
FROM AllData
ORDER BY [Name], [Date];

As also pointed out, the problem with this solution occurs if there are two or more consecutive rows with NULL in Location.
To see this, try the above query again after adding:

INSERT INTO #t2
VALUES ('20220103','Apple',5,NULL)
	,('20220107','Apple',5,NULL);

One way around the consecutive nulls problem is:

WITH AllData
AS
(
	SELECT [Date], [Name], qty, [Location] FROM #t1
	UNION ALL
	SELECT [Date], [Name], qty, [Location] FROM #t2
)
SELECT [Date], [Name], qty
	,[Location] AS Original_Location
	,COALESCE
	(
		[Location]
		,SUBSTRING
		(
			MAX(CONVERT(char(8), [Date], 112) + [Location])
				OVER (PARTITION BY [Name] ORDER BY [Date])
			,9, 100
		)
	) AS New_Location
FROM AllData
ORDER BY [Name], [Date];