Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Group FICO by bin over <amount>

Author  Topic 

jwi71
Starting Member

3 Posts

Posted - 2013-04-02 : 20:57:58
Have been tasked with looking at some FICO data but I'm new and somewhat stuck.

We have loan applications which upon approval become a contract from our customers (vendors in the DB).

I have been tasked with looking at all customers who have > 60 contracts in a calendar year. Then taking those customers (with more than 60 contracts) and grouping those contracts into 5 FICO bins (<400, 401-500, 501-600, 601-700,>700) along with a total bin based upon that contracts FICO score.

I have a vendor column, fico score column, and a contract column.

So...how do I get the fico score into those bins for contracts when the vendor has > 60 in a year (and ignore others) (its for a special price program)?

I can get the FICO scores into bins with a select case. I can also get the number of vendors with contracts over 60...but when trying to get both...ugh. So, I can get bins, but for everyone and not just those with more than 60 contracts - or I get the vendors with more than 60 contracts - just not FICO into the bins. Sigh.

Looking for whatever guidance I can get from you kind folks...I'll try to be more helpful or provide more info if needed. Its a SQL2012 box.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-04-03 : 04:08:24
Some sample data and expected output?



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

jwi71
Starting Member

3 Posts

Posted - 2013-04-03 : 08:55:13
desired output
vendor_name Ficoband Apps Contracts
JOE'S CAR SHOP <= 500 49 5
JOE'S CAR SHOP 501 to 600 207 30
JOE'S CAR SHOP 601 to 700 225 33
JOE'S CAR SHOP 701 + 45 8
JOE'S CAR SHOP Missing 133 3
JOE'S CAR SHOP Total 659 79


sample data

FICO vendor Contract_Count Application_Count
NULL JOE'S CAR SHOP 0 1
NULL JOE'S CAR SHOP 0 1
538 JOE'S CAR SHOP 0 1
NULL JOE'S CAR SHOP 0 1
785 JOE'S CAR SHOP 0 1
573 JOE'S CAR SHOP 0 1
612 JOE'S CAR SHOP 0 1

Apologies for the formatting, I tried to get it to look better but couldn't figure a way - and it looked fine when I type the post but the formatting gets lost.

Thats it in a nutshell.

Any help would be appreciated.
Go to Top of Page

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-04-03 : 09:23:51
provide the output in light of the given smaple data. As I don't understand the logic desired output values of (Apps, Contracts) which are 49,5 in the first row. How is that generated using that sample data?



Cheers
MIK
Go to Top of Page

jwi71
Starting Member

3 Posts

Posted - 2013-04-03 : 09:56:00
The code I am using is to get the output data is below.
IT gives me the sample output. The only problem, of course, is it captures all vendors when I only want vendors who have > 60 contracts in a year.

I think that will show how I am getting those count numbers.

drop table #temp1				
select a.FICO_Nbr,
b.vendor_name,
[Contract_Count] = case when Contract_Received_Date is not Null then 1 else 0 end,
[Application_Count] = case when Initial_Received_Date is not NULL or Approval_Status_Ind = 'Y' then 1 else 0 end,
[Ficoband] = (CASE WHEN FICO_Nbr IS NULL OR FICO_Nbr = 0 THEN ' Missing'
WHEN FICO_Nbr <= 500 THEN ' <= 500'
WHEN FICO_Nbr BETWEEN 501 AND 600 THEN ' 501 to 600'
WHEN FICO_Nbr BETWEEN 601 AND 700 THEN ' 601 to 700'
WHEN FICO_Nbr >= 701 THEN ' 701 +' END)
into #temp1
from Vw_Application_Dim a
inner join vw_vendor_dim b
on a.vendor_key = b.vendor_key
where Initial_Received_Date between '2012-01-01' and '2013-01-01'


select vendor_name,
[Ficoband],
Apps = count(case when [Application_Count] = 1 then [Application_Count] else null end),
Contracts = count(case when [Contract_Count] = 1 then [Contract_Count] else null end)
from #temp1
group by vendor_name, [Ficoband]

union

select vendor_name,
[ficoband] = ' Total',
Apps = count(case when [Application_Count] = 1 then [Application_Count] else null end),
Contracts = count(case when [Contract_Count] = 1 then [Contract_Count] else null end)
from #temp1
group by vendor_name
order by vendor_name

Go to Top of Page
   

- Advertisement -