Click here to Skip to main content
15,867,704 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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]
Posted
Updated 21-Feb-22 6:35am

Try this

SQL
select t.companyid,t.[year],cast(cast(IsNULL(c.cnt,0) as decimal)/
        cast(t.cnt as decimal) as decimal(18,2)) as totalpercentage   
from #tabletotal t
 left outer join #tableconfirment c on t.companyid=c.companyid and t.[year]=c.[year]
 
Share this answer
 
v2
Comments
Maciej Los 21-Feb-22 12:31pm    
5ed!
In addition to solution #1 by _Asif_, i'd suggest to read this: Visual Representation of SQL Joins[^]
On the other hand... Say NO to Venn Diagrams When Explaining JOINs – Java, SQL and jOOQ.[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900