CODEWITHSUNDEEP
sqloracle-database
DigitalSea
DigitalSea

Reputation: 191

SQL: If null get default value from table B based on identifiers from table A

Wondering if anyone can give me hints on how to best solve this query problem.

I have two tables that can be joined by benefit code and company code. Only table A has employee number.

Table A - Has employee data and their benefit amounts and codes. If null then look to Table B for default amount of the benefit code.

Table B - Has company default amounts of the benefit codes.

Below is what I'm trying to get at. Only the highlighted are summed. Not sure the best approach will be to get the results I need. I've tried multiple ways. I created a union between the two thinking I can provide some logic on the outer query but I'm hitting a road block again.

enter image description here

Select 
*
from (
SELECT
    EMB_EMP_NO
  , EMB_COMP_CODE
  , EMB_BEN_CODE
  ,   SUM(
    CASE
      WHEN EMB_BEN_CODE IN ('9600', '9601', '9602', '9603', '9604')
      AND EMB_BEN_TYPE  = 'P' AND EMB_BEN_AMOUNT IS NOT NULL
      THEN (EMB_BEN_AMOUNT / 100)
    ELSE 0
  END) AS "53RDBURDEN_PERC" 
  FROM
    da.pyempben t3
  WHERE
    EMB_BEN_CODE   IN ('9600', '9601', '9602', '9603', '9604')
  AND EMB_END_DATE IS NULL
  AND EMB_COMP_CODE = 'ACC'
  AND EMB_EMP_NO = '8687'
  GROUP BY
    EMB_EMP_NO
  , EMB_COMP_CODE
  , EMB_BEN_CODE
union


SELECT
    'DEFAULT' AS "DEFAULT_BENCODE"
   , COB_COMP_CODE                                              AS "ACPYSALRPTC_COMPCODE"
   , COB_BEN_CODE                                               AS "ACPYSALRPTC_BENCODE"    
   ,   
    CASE
      WHEN COB_BEN_CODE IN ('9600', '9601', '9602', '9603', '9604')
      AND COB_BEN_TYPE  = 'P'
      THEN (COB_BEN_AMOUNT / 100)
    ELSE 0
  END                                                           AS "ACPYSALRPTC_53RDBURDPERC"   
  FROM
    DA.PYCOMBEN
  WHERE
    COB_BEN_CODE   IN ('9600', '9601', '9602', '9603', '9604')
  AND COB_END_DATE IS NULL
  AND COB_PRN_CODE  = 'WK'
  and cob_comp_code = 'ACC'
  ) src
 ;

enter image description here

Upvotes: 0

Views: 32

Answers (1)

Brayden
Brayden

Reputation: 201

SELECT
  a.EMB_EMP_NO                                      AS EMP_NO,
  a.EMB_COM_CODE                                    AS Comp_Code,
  a.Benefit_Name,
  SUM(COALESCE(a.Benefit_Amount, b.Default_Amount)) AS total_amount
FROM
  table_a a
JOIN
  table_b b
ON
  a.Benefit_Code = b.Benefit_Code
AND
  a.EMB_COMP_CODE = b.COB_COMPCODE
GROUP BY
  a.EMB_EMP_NO,
  a.EMB_COM_CODE,
  Benefit_Name
;

How about using SUM(COALESCE())? Good luck!

Upvotes: 1

Related Questions