June 20, 2017 at 11:03 pm
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
June 21, 2017 at 2:14 am
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
June 21, 2017 at 10:20 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply