year 2020 not display when divideing two values from two table ?

Question

i work on sql server 2014 i face issue year 2020 notr display when divide two values from two tables

so i divide all data for same company and year

so year 2020 not have row on table #tableconfirment

so if missing year i will suppose it will be 0

so how to handle that please

create table #tabletotal  
(  
companyid int,  
[year] int,  
cnt int  
)  
insert into #tabletotal(companyid,[year],cnt)  
select 1200,2015,20  
union  
select 1200,2016,25  
union  
select 1200,2017,30  
union  
select 1200,2018,15  
union  
select 1200,2019,12  
union  
select 1200,2020,10  
--drop table #tableconfirment  
create table #tableconfirment  
(  
companyid int,  
[year] int,  
cnt int  
)  
insert into #tableconfirment(companyid,[year],cnt)  
select 1200,2015,4  
union  
select 1200,2016,8  
union  
select 1200,2017,12  
union  
select 1200,2018,11  
union  
select 1200,2019,10  
  
  
select t.companyid,t.[year],t.cnt  
       / c.cnt as totalpercentage  from #tabletotal t  
inner join #tableconfirment c on t.companyid=c.companyid and t.[year]=c.[year]  

expected result

Answer 1

Just to be clear on Olaf's answer.

You are using an INNER JOIN on #tableconfirment which does not contain a matching row for 2020. If you change INNER JOIN to OUTER JOIN, you will get all the rows from #tabletotal and NULL for 2020, which you then need to handle.

Answer 2

Use an OUTER JOIN (Left) with a CASE condition:

select t.companyid,t.[year], 
       case when c.cnt IS NULL then 0 ELSE t.cnt / c.cnt END as totalpercentage  
from #tabletotal t
     left join 
     #tableconfirment c on t.companyid=c.companyid and t.[year]=c.[year]