When divide missing values from two tables it not display ?

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  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, 9 months ago by  ahmed_elbarbary.2010.

• ratbak

  Ten Centuries

  Points: 1379

  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

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  February 21, 2022 at 10:43 pm

  #3986609

  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)