Hello MS SQL Server / TSQL Experts, help me save some brain cells on this one..

Three tables:

creditcustomer:
customer char10 (unique key)
bank     char10
hobby    char10

creditbank:
bank      char10 (unique key)
limit     money

creditpurchase:
customer char10 (many)
amount   money

I want to report every customer who has spent more than the bank limit
in format:

customer, bank, hobby,(amount over limit)

so far I can report I have this much:

customer, bank, hobby, (total amount) 

using the following SQL:

select creditcust.customer, creditcust.bank, creditcust.hobby,
x=sum(amount) from creditcust
join creditpurchase on creditcust.customer = creditpurchase.customer
group by creditcust.customer, creditcust.bank, creditcust.hobby order
by sum(amount)

I know I must be very close - it should be juicy.

TIA

---

Request for Question Clarification by aditya2k-ga on 09 Nov 2002 01:20 PST

Hi grekenski,

Do you want only the customers who have crossed limit? If so, assuming
the limit is 100,000, the query is
select creditcust.customer, creditcust.bank, creditcust.hobby,
x=100000 - sum(amount) from creditcust
join creditpurchase on creditcust.customer = creditpurchase.customer 
group by creditcust.customer, creditcust.bank, creditcust.hobby 
order by x where x > 0 

Pls tell me if the above works.

---

Clarification of Question by grekenski-ga on 09 Nov 2002 05:24 PST

The limit is set by bank in creditbank.limit

so creditcustomer.customer "Jane", with bank "Citibank" would be over
her limit if :


creditbank.bank = Citibank
creditbank.limit = $5000 

and she has creditpuchases.amount(s) summing up to $6000 (for
example).

her report would look like this

customer, bank, hobby, amount-over-limit
Jane, citibank, skiing, $1000 


Thank you.

---

Request for Question Clarification by aditya2k-ga on 09 Nov 2002 07:05 PST

I think this should do (syntax may be wrong, but the logic is correct)
:

select creditcust.customer, creditcust.bank, creditcust.hobby,
x=(select creditbank.limit from creditbank,creditcust where
creditcust.bank = creditbank.bank) - sum(amount) from creditcust
join creditpurchase on creditcust.customer = creditpurchase.customer
group by creditcust.customer, creditcust.bank, creditcust.hobby

---

Clarification of Question by grekenski-ga on 09 Nov 2002 07:48 PST

extremely close now - had to remove the ",creditcust"  on the x=
equate

select creditcust.customer, creditcust.bank, creditcust.hobby, 
x=(select creditbank.limit from creditbank where
creditcust.bank = creditbank.bank) - sum(creditpurchase.amount) from
creditcust join creditpurchase on creditcust.customer =
creditpurchase.customer
group by creditcust.customer, creditcust.bank, creditcust.hobby 

this produces every record with the right sums, but I do  only need
records over limit. I tried to place the where x<0 (as below) but got
:

Invalid column name 'x'.


select creditcust.customer, creditcust.bank, creditcust.hobby, 
x=(select creditbank.limit from creditbank where
creditcust.bank = creditbank.bank) - sum(creditpurchase.amount) from
creditcust  join creditpurchase on creditcust.customer =
creditpurchase.customer
where x < 0 group by creditcust.customer, creditcust.bank,
creditcust.hobby

Thank you very much

---

Clarification of Question by grekenski-ga on 09 Nov 2002 08:47 PST

got it!

select * from (select creditcust.customer, creditcust.bank,
creditcust.hobby,
x=(select creditbank.limit from creditbank where
creditcust.bank = creditbank.bank) - sum(creditpurchase.amount) from
creditcust join creditpurchase on creditcust.customer =
creditpurchase.customer
group by creditcust.customer, creditcust.bank, creditcust.hobby)
temptable
where x<0

thanks bunch for pointing me in the right direction. trying to figure
out how to accept your last answer!

Hi grekenski,

  I'll now post this as an official answer since you mentioned you
were trying to figure out how to accept it.

My original suggestion was :
select creditcust.customer, creditcust.bank, creditcust.hobby, 
x=(select creditbank.limit from creditbank,creditcust where
creditcust.bank = creditbank.bank) - sum(amount) from creditcust
join creditpurchase on creditcust.customer = creditpurchase.customer
group by creditcust.customer, creditcust.bank, creditcust.hobby

which lead you to the final answer:
select * from (select creditcust.customer, creditcust.bank,
creditcust.hobby,
x=(select creditbank.limit from creditbank where 
creditcust.bank = creditbank.bank) - sum(creditpurchase.amount) from
creditcust join creditpurchase on creditcust.customer =
creditpurchase.customer
group by creditcust.customer, creditcust.bank, creditcust.hobby)
temptable
where x<0 

Thank you for using this service and have a nice day

Warm Regards,
aditya2k

Thank you!!!!  I am totally blow away by the very concept of this service.