SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Group FICO by bin over <amount>
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jwi71
Starting Member

3 Posts

Posted - 04/02/2013 :  20:57:58  Show Profile  Reply with Quote
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

Sweden
30282 Posts

Posted - 04/03/2013 :  04:08:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 04/03/2013 :  08:55:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 04/03/2013 :  09:23:51  Show Profile  Reply with Quote
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 - 04/03/2013 :  09:56:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000