troubleshooting Question

select stmt multiply function

Avatar of fahVB
fahVB asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
11 Comments2 Solutions463 ViewsLast Modified:
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_Score*Balloon*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_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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros