Help with Query #2

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.