Grouping based on minimums

[stephen.h.dow]

So, I have a series of loans (eg, mortgages) in one table - the loan
data include credit scores for the holders of the loan). In another
table, I have segments based on credit score. I need to create a query
that groups the loans by segment. So..

Loan Table:
ID FICO_1 FICO_2 Amount
1 750 699 100,000
2 650 700 200,000
3 750 710 222,000
4 676 699 300,000
5 720 729 150,000

Segment Table:
FICO_1 FICO_2 Segment
720 700 Good
700 680 OK
680 660 Fair
660 640 Warning
640 620 Bad

So, i need a query that puts a loan into the highest segment based on
meeting both scores:
Segment Amount
Good 373,000
OK 100,000
Fair 0
Warning 300,000
Bad 200,000

Thanks for any help - much appreciated.

 

[KARL DEWEY]

Maybe others can follow your data conversion but I did not.

Can you explain a little more how you got from the two tables to your output?

 

[stephen.h.dow]

You basically take each loan and run it through the segment table, and
assign the loan to the first segment where both loan fico's are above
both segment fico's.

So, Loan ID 1 is "OK" because FICO_2 meets the "OK" criteria (even
though FICO_1 meets the "Good" criteria).

I'm also open to using a function if people think that is a way to go.
However, some FICO's may be null, so it may be tough to pass that to a
function.

Thanks

 

[stephen.h.dow]

Actually, I think i figured it out - using a function. The code is a
little long, because there are a few other factors I was solving for,
but I'm happy to share it with anyone who has a similar problem.