how can I use the new variable created by case when

• kkbear

  SSC-Addicted

  Points: 457

  More actions

  June 20, 2017 at 11:03 pm

  #322190

  I have two variables SCR_1, SCR_2 created by case when sentence. Is there a way I can use them in the same query to get another variable TOT= SCR_1+SCR_2? Thanks.

  SELECT a.[Account_ID_nbr]
               ,[SCR_1]=
        (case when a.MAX_PCT_ACCTS_LST_QTRb<0.14 then 58
      when (0.14<=a.MAX_PCT_ACCTS_LST_QTRb and b.MAX_PCT_ACCTS_LST_QTRb <0.5) then 20
      when 0.5<=a.MAX_PCT_ACCTS_LST_QTRb then -14
      else 22
      end
      )
                ,[SCR_2]=
     (case when a.n_pmt_Y0<2 then -13
      when (2<=a.n_pmt_Y0 and a.n_pmt_Y0<6) then 24
      when (6<=a.n_pmt_Y0 and a.n_pmt_Y0<21) then 43
      when (21<=a.n_pmt_Y0 and a.n_pmt_Y0<38) then 63
      when 38<=a.n_pmt_Y0 then 127
      end
      )

  from a

• Thom A

  SSC Guru

  Points: 99126

  More actions

  June 21, 2017 at 2:14 am

  #1947506

  You say your have variables, but these are column names in your query. Variables start with an @ at the beginning.

  You could use a CTE to do this if you wanted:
  WITH CTE AS (
  SELECT a.Account_ID_nbr,
        CASE WHEN a.MAX_PCT_ACCTS_LST_QTRb < 0.14 THEN 58
              WHEN a.MAX_PCT_ACCTS_LST_QTRb >= 0.14 AND b.MAX_PCT_ACCTS_LST_QTRb < 0.5 THEN 20
              WHEN a.MAX_PCT_ACCTS_LST_QTRb >= 0.5 THEN -14
              ELSE 22 END AS SCR_1,
        CASE WHEN a.n_pmt_Y0<2 then -13
              WHEN a.n_pmt_Y0 >= 2 AND a.n_pmt_Y0 < 6 THEN 24
              WHEN a.n_pmt_Y0 >= 6 AND a.n_pmt_Y0 < 21 THEN 43
              WHEN a.n_pmt_Y0 >= 21 AND a.n_pmt_Y0 < 38 THEN 63
              WHEN a.n_pmt_Y0 >= 38 THEN 127 END AS SCR_2
  FROM a )
SELECT CTE.Account_ID_nbr,
         CTE.SCR_1,
         CTE.SCR_2,
         CTE.SCR_1 + CTE.SCR_2 AS TOT
  FROM CTE;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ScottPletcher

  SSC Guru

  Points: 100947

  More actions

  June 21, 2017 at 10:20 am

  #1947596

  You can use APPLY to effectively assign alias names to expressions / results.  You can even nest APPLYs so that the alias from one is used in the next one.

  
SELECT a.[Account_ID_nbr]
,ca.[SCR_1]
,ca.[SCR_2]
,TOT=ca.[SCR_1]+ca.[SCR_2]
from a
cross apply (
  select
  [SCR_1]=
  (case when a.MAX_PCT_ACCTS_LST_QTRb<0.14 then 58
  when (0.14<=a.MAX_PCT_ACCTS_LST_QTRb and b.MAX_PCT_ACCTS_LST_QTRb <0.5) then 20
  when 0.5<=a.MAX_PCT_ACCTS_LST_QTRb then -14
  else 22
  end),
  [SCR_2]=
  (case when a.n_pmt_Y0<2 then -13
  when (2<=a.n_pmt_Y0 and a.n_pmt_Y0<6) then 24
  when (6<=a.n_pmt_Y0 and a.n_pmt_Y0<21) then 43
  when (21<=a.n_pmt_Y0 and a.n_pmt_Y0<38) then 63
  when 38<=a.n_pmt_Y0 then 127
  end)
) as ca

  --nested version, just as an example
SELECT a.[Account_ID_nbr]
,ca.[SCR_1]
,ca.[SCR_2]
,ca2.[TOT]
from a
cross apply (
  select
  [SCR_1]=
  (case when a.MAX_PCT_ACCTS_LST_QTRb<0.14 then 58
  when (0.14<=a.MAX_PCT_ACCTS_LST_QTRb and b.MAX_PCT_ACCTS_LST_QTRb <0.5) then 20
  when 0.5<=a.MAX_PCT_ACCTS_LST_QTRb then -14
  else 22
  end),
  [SCR_2]=
  (case when a.n_pmt_Y0<2 then -13
  when (2<=a.n_pmt_Y0 and a.n_pmt_Y0<6) then 24
  when (6<=a.n_pmt_Y0 and a.n_pmt_Y0<21) then 43
  when (21<=a.n_pmt_Y0 and a.n_pmt_Y0<38) then 63
  when 38<=a.n_pmt_Y0 then 127
  end)
) as ca
cross apply (
  select
  [TOT]=ca.[SCR_1]+ca.[SCR_2]
) as ca2

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.