When divide missing values from two tables it not display ?

Question

Post reply

When divide missing values from two tables it not display ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3070

February 21, 2022 at 10:32 am

Go to Answer

#3986321

i work on sql server 2012 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

companyidyeartotalpercentage
1200       20150.2
1200       20160.32
1200       20170.4
1200       20180.73
1200       20190.83
1200       20200

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]

This topic was modified 2 years, 7 months ago by ahmed_elbarbary.2010.

ratbak

SSC Eights!

Points: 939
More actions
February 21, 2022 at 2:13 pm
Answer
#3986426

If it's truly just (and always) 2020 that is/will be missing then just hard code a union all with 2020

If there are multiple years and/or some dynamic logic that determines what years need to be included, and you don't definitively know what years aren't included, then you will need to union all a select from a year table, a table-valued function or variable, etc. with a not exists clause to exclude those that are included in #tableconfirment & #company

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Thank you for the readily consumable data. This should do it for you and it contains some protection from "Divide by Zero" issues...

 SELECT   companyid      = ISNULL(t.companyid,c.companyid)
        ,[year]          = ISNULL(t.[year],c.[year])
        ,ConfirmCnt      = ISNULL(c.cnt+0.0,0)       --I included this just for demo purposes
        ,TotalCnt        = ISNULL(NULLIF(t.cnt,0),1) --I included this just for demo purposes
        ,TotalPercentage = CONVERT(DECIMAL(18,2),ISNULL(c.cnt+0.0,0)/ISNULL(NULLIF(t.cnt,0),1))
   FROM      #tabletotal      t
   FULL JOIN #tableconfirment c  ON t.companyid = c.companyid 
                                AND t.[year]    = c.[year]
;

Results (including the columns I added for demo purposes, which may be removed for your final code)...

Lemme know if you have any questions about the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions