Hi. All
I am having trouble quering #2. No problem with #1(there is product & sub product code) & data was taken from 1 tble for #1.. Data is also avail on another table as well.
Cust has many product, but 1 cust_id.
Need your help guys..
JK
1) Select customers with Free Checking, Gold Checking,ect... with combined balance $11,000 - $25,000.
2) Select all customers who have an IXI(Total Relationship Balance) value at least twice the amount of their combined balance of $11K - $25. For example, if a customer has $11,000 in combined balance, their IXI must be $22,000+.
3) How many customers fall into this bucket?
How about offering us the table DDL, your attempted queries so far, and perhaps some sample data in the form of INSERT statements.
|||Hi Arnie,
This is what I did for step one:
step #1
select cust_id, acct_num, sys_prod_cd, sys_sub_prod_cd, cur_book_bal into lp.dbo.RTP_JK_IXIDepBal_19939
from RTP_Cust_Data_Final
where sys_prod_cd+sys_sub_prod_cd in ('CDA43','CDA49','DDAU7',
'RSV20','DDA86') and cur_book_bal >= 11000and cur_book_bal <= 25000
Step1.1
select count(distinct cust_id), sum(cur_book_bal)from RTP_temp_JK_IXIDepBal_19939
group by cust_id
order by Cust_id
Step 2 is where I am having problem. What I am going to do is to run them individually to get a waterfall count of potential cust count.
Thanks
JK(john)
|||With my interpretation of #1, I don't think your query will give the correct result, but then I don't know the definitions of your fields.
So, here's a rewrite of #1 and guess at #2 since i didn't know where the IXI-relationship-balance comes from.
Code Snippet
--#1
select cust_id,
sum(cur_book_bal)as combined_balance
from RTP_Cust_Data_Final
where sys_prod_cd+sys_sub_prod_cd in('CDA43','CDA49','DDAU7','RSV20','DDA86')
groupby cust_id
havingsum(cur_book_bal)between 11000 and 25000
--#2
select cust_id,
sum(cur_book_bal)as combined_balance,
sum(ixi_balance)as ixi_balance
from RTP_Cust_Data_Final
groupby cust_id
havingsum(cur_book_bal)between 11000 and 25000
andsum(ixi_balance)=>(sum(cur_book_bal)*2)
|||Hi Dale.J
IXI-relationship Balance mean the total sum bal of All (each) account cur_bal the cust has with us.
(sum(cur_book_bal) as IXI_balance),
then if that balance fall B/ween 11000 & 25000
multiply those bal by 2 & fetch those cust_id that fall within that bal, as the result set.
Im trying out ur query as well & twek it as I go. But this is what I am trying to get.
Thank U.
JK
|||You're welcome.
If you need more help with the tweaking, just post a little more DDL, etc. and some sample (insert) data.
BTW, does the RTP prefix stand for Research Triangle Park?
|||
RTP=Name of project team.
JK
|||Ah, yes.
I thought maybe you were here in the Triangle.
