Add two columns when one is the output of a Case statement

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  December 4, 2018 at 5:07 pm

  #369220

  I need to add the T.Duty Rate to the Chap_99_Rate.  Please advise.

  SELECT
l.HTS_99_Num,
    L.Ctry_Origin,    
T.DutyRate,
T.Chap99,
CASE
WHEN T.Chap99 = '9903.88.01'
     THEN '.25'
WHEN T.Chap99 = '9903.88.02'
     THEN '.25'
WHEN T.Chap99 = '9903.88.03'
     THEN '.10'
WHEN T.Chap99 = '9903.88.04'
     THEN '.10'
   END as [Chap 99 Rate]

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  December 4, 2018 at 5:11 pm

  #2015518

  NOTE: I suppose i could put the case output into a  temp table, but kind of messy for me. thanks

• Phil Parkin

  SSC Guru

  Points: 247180

  More actions

  December 4, 2018 at 5:19 pm

  #2015520

  jeffshelix - Tuesday, December 4, 2018 5:07 PM

  I need to add the T.Duty Rate to the Chap_99_Rate.  Please advise.

  SELECT
l.HTS_99_Num,
    L.Ctry_Origin,    
T.DutyRate,
T.Chap99,
CASE
WHEN T.Chap99 = '9903.88.01'
     THEN '.25'
WHEN T.Chap99 = '9903.88.02'
     THEN '.25'
WHEN T.Chap99 = '9903.88.03'
     THEN '.10'
WHEN T.Chap99 = '9903.88.04'
     THEN '.10'
   END as [Chap 99 Rate]

  CROSS APPLY should help here:

  SELECT l.HTS_99_Num,
   l.Ctry_Origin,
   T.DutyRate,
   T.Chap99,
   [Chap 99 Rate] = res.Chap99Rate,
   AnotherColumn = T.DutyRate + res.Chap99Rate
FROM SomeTable
  CROSS APPLY
(
  SELECT Chap99Rate = CASE T.Chap99
          WHEN '9903.88.01' THEN
           '.25'
          WHEN '9903.88.02' THEN
           '.25'
          WHEN '9903.88.03' THEN
           '.10'
          WHEN '9903.88.04' THEN
           '.10'
        END
) res;

  ---