i work on sql server 2014 i face issue year 2020 not 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
sample data
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
expected result
companyid year totalpercentage
1200 2015 0.2
1200 2016 0.32
1200 2017 0.4
1200 2018 0.73
1200 2019 0.83
1200 2020 0
What I have tried:
select t.companyid,t.[year],cast(cast(c.cnt as decimal)/
cast(t.cnt as decimal) as decimal(18,2)) as totalpercentage from #tabletotal t
inner join #tableconfirment c on t.companyid=c.companyid and t.[year]=c.[year]