I need an SQL statement for this... [message #20577] |
Wed, 05 June 2002 14:21 |
Kyle
Messages: 4 Registered: December 2000
|
Junior Member |
|
|
3 tables:
Lost_Acct (Client_No, Termin_Date, other unrelated columns)
Client Reason (Client_No, Reason_Code) -- this table lists all of the client_no's and their respective reason_codes
Reason (Reason_Code, Reason_desc) -- this table lists all of the reason codes and their description (there's 13 of them)
I need an SQL statement that will give me this in lamen's terms:
For each reason code (1 through 13), show me a *count* of clients that have that reason_code and who's termin_date is between Jan 2000 and December 2000. Any of you SQL gurus able to help me out?
|
|
|
Re: I need an SQL statement for this... [message #20579 is a reply to message #20577] |
Wed, 05 June 2002 14:57 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
This might be a starting point:
select r.reason_code, r.reason_desc, count(la.client_no)
from reason r, client_reason cr,
(select client_no
from lost_acct
where to_char(termin_date, 'yyyy') = '2000') la
where cr.reason_code (+)= r.reason_code
and la.client_no (+)= cr.client_no
group by r.reason_code, r.reason_desc;
|
|
|