Union tables and fill null with last not null

1

Hi,
I'm new to SQL world. I have question hope I get answer here :slightly_smiling_face:

I have 2 tables with same headers. first i want them to Union then i would like to fill Null with last not Null value.

if anyone could be able to help me with this, that would be great.
Please see below image as an example.

sql query
sql query437×529 50.5 KB

Thanks,
Rollno.21

2

hi

did this using row number with (select null) which is not recommended
as it does not guarentee the order by

preferred way would be to create an identity column in Table1 and Table2 and then populate the data

create the data script 
drop table #Table1
create table #Table1 ( Name varchar(100),qty int,Location varchar(100) null) 
insert into #Table1 select 'Apple',2,'1st row'
insert into #Table1 select 'orange',10,'2nd row'
insert into #Table1 select 'Apple',5,null
insert into #Table1 select 'Kiwi',1,'10th row'
select * from #Table1

drop table #Table2
create table #Table2 ( Name varchar(100),qty int,Location varchar(100) null) 
insert into #Table2 select 'orange',9,null
insert into #Table2 select 'apple',10,'3rd row'
insert into #Table2 select 'Kiwi',4,null
select * from #Table2

; with cte as 
(
	select * from #Table1
	union all 
	select * from #Table2
) 
, cte_notnull as 
(
select row_number() over(order by (select null)) as rn , *  from ( select * from  cte where Location is not null ) a 
) 
, cte_null as 
( select row_number() over(order by (select null)) as rn , *  from ( select * from  cte where Location is  null ) a 
) 
select 
    c.Name
   ,c.qty
   ,isnull(c.location,a.location) 
from 
  cte_notnull a       join cte_null b on a.rn   = b.rn 
                right join cte c      on b.Name = c.Name and b.qty = c.qty

image

3

Thanks Harish,

I have added dates to it, now it is easy to add serial numbers to the table I assume.
could you please work on it?

sql query
sql query437×529 50.5 KB

4

Totally inappropriate.

5

Sorry I'm new to SQL world, i do not know how things work. so please help me

6

please post the image data as

create table
insert data into table

statements

example

create table #Table1 ( Name varchar(100),qty int,Location varchar(100) null) 
insert into #Table1 select 'Apple',2,'1st row'
insert into #Table1 select 'orange',10,'2nd row'
insert into #Table1 select 'Apple',5,null
insert into #Table1 select 'Kiwi',1,'10th row'
7

Hi Harish,

did I understand correct?
Please see below, thanks for your help

create table #Table1 ( Start_Date date, Name varchar(100),qty int,Location varchar(100) null)
insert into #Table1 select '1/1/2022','Apple',2,'1st row'
insert into #Table1 select '1/1,2022','orange',10,'2nd row'
insert into #Table1 select '2/1//2022','Apple',5,null
insert into #Table1 select '2/1/2022','Kiwi',1,'10th row'

create table #Table2 ( Start_Date date, Name varchar(100),qty int,Location varchar(100) null)
insert into #Table2 select '3/1/2022','orange',9,null
insert into #Table2 select '4/1,2022','Apple',10,'3rd row'
insert into #Table2 select '5/1//2022','kiwi',4,null
insert into #Table2 select '5/1/2022','Apple',2,'5th row'
insert into #Table2 select '6/1/2022','Apple',5,null

8

yes you understood it correctly

my SQL Query which i gave you before
will work even after you add dates

9

well, if dates are changed let's say latest date in table1 and old date in table2 will it work?
because i want them to arrange as per dates not as per data set order available in the tables.

10

; with cte as
(
select * from #Table1
union all
select * from #Table2
)
, cte_notnull as
(
select row_number() over(order by (select null)) as rn , * from ( select * from cte where Location is not null ) a
)
, cte_null as
( select row_number() over(order by (select null)) as rn , * from ( select * from cte where Location is null ) a
)
select
c.Start_Date
,c.Name
,c.qty
,isnull(c.location,a.location)
from
cte_notnull a join cte_null b on a.rn = b.rn
right join cte c on b.Name = c.Name and b.qty = c.qty
Order by C.Start_Date

12

Thanks, let me try this

13

Sorry Harish, this is not working with my original data. I might have missed something here.
if I copy above code as it is, will that work?

14 
WITH AllData
AS
(
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table1
	UNION ALL
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table2
)
SELECT [Start_Date], [Name], qty
	,COALESCE([Location], LAG([Location]) OVER (PARTITION BY [Name] ORDER BY [Start_Date])) AS [Location]
FROM AllData;

or if there are multiple consecutive nulls:

WITH AllData
AS
(
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table1
	UNION ALL
	SELECT [Start_Date], [Name], qty, [Location] FROM #Table2
)
SELECT [Start_Date], [Name], qty
	,COALESCE
	(
		[Location]
		,SUBSTRING
		(
			MAX
			(
				CONVERT(char(8), [Start_Date], 112) + [Location])
					OVER (PARTITION BY [Name] ORDER BY [Start_Date]
					
			)
			,9, 100
		)
	) AS [Location]
FROM AllData;

With SQL 2022 you can also use LAST_VALUE IGNORE NULLS.

15

Hi Ifor, Thanks for your help :slightly_smiling_face:,

I think it works as expected, but how can I compare with my old location to forward filled location?
when I checked both location columns have exact same values in the output.

16

Hi, Any Help, please?

17

If you want help on a forum you have to ask a cogent question. I for one have no idea what you want help with.

18

Thanks, I will try to give data in table format :slight_smile: