fahVB
asked on
select stmt multiply function
This multiplication in select stmt is working but when one of the value is zero then the required result in Risk_Score becomes zero...
select LTV_Score*FICO_Score*ST_Sc ore*Balloo n*Interest _Only as Risk_Score
In the example below, this second account should score 12 [2*1*1*2*3=12] but because of the 0 in FICO score, it came out 0 [2*0*1*2*3=0]
LTV_Score FICO_Score ST_Score Balloon Interest_Only Risk_Score
1 1 1 2 3 6
2 0 1 2 3 0
2 1 1 0 3 0
1 1 1 0 3 0
2 3 1 0 3 0
1 1 2 0 3 0
1 3 1 0 3 0
is there a way to ignore zer0's and continue with the multiplication step
select LTV_Score*FICO_Score*ST_Sc
In the example below, this second account should score 12 [2*1*1*2*3=12] but because of the 0 in FICO score, it came out 0 [2*0*1*2*3=0]
LTV_Score FICO_Score ST_Score Balloon Interest_Only Risk_Score
1 1 1 2 3 6
2 0 1 2 3 0
2 1 1 0 3 0
1 1 1 0 3 0
2 3 1 0 3 0
1 1 2 0 3 0
1 3 1 0 3 0
is there a way to ignore zer0's and continue with the multiplication step
select LTV_Score*FICO_Score*ST_Score*Balloon*Interest_Only as Risk_Score
from
(select
case when current_ltv < 85.01 then 1 when current_ltv < 95.01 then 2 else 3 end as LTV_Score,
case when FICO < 640 then 3 when FICO < 700 then 2 when FICO IS NULL then 0 else 1 end as FICO_Score,
case when State in ('CA','FL','AZ','NV') then 3 when State = 'NJ' then 2 else 1 end as ST_Score,
case when Balloon_Date > '1/1/1900' then 2 when Balloon_Date = '1/1/1900' then 0 when Balloon_Date IS NULL then 0 else 1 end as Balloon,
case when Interest_Only_Flag = 'Y' then 3 else 1 end as Interest_Only
From First_Mortages
) A
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
..what a good idea. ;)
GENIUS.... PURE GENIUS!
Maybe they're right..maybe we are twins.
Siamese twins: cheesemandew
:) I actually laughed out loud on that one...
ASKER
Aight, let me resolve the dispute between twins
Not a dispute... we're just having fun at the expense of cluttering up a thread!
:)
:)
ASKER
I know, I am just being funny....
ASKER
Thank you for your suggestions, twins....