April 20, 2005 at 2:02 pm
I am working with an existing SP that I need to tweak a little. My problem is I am fairly new to SQL.
Here is the code I'm working with:
ALTER PROCEDURE cons_ln_runoff_2_sp
AS
SELECT right(B.loan_type, 2) as ln_type,
B.ln_Type_Input_Code,
C.lntyp_Desc,
sum(B.ln_Orig_Amt) as orig_amt,
B.ln_Close_Date,
sum(trans_activity.tx_Prin_Paid) as principal_paid,
count(B.loan_type) as nbr_loans,
Acct_ln_group_tbl.[Group],
B.asof_date
FROM loan B INNER JOIN
ln_type C ON B.Loan_Type = C.Loan_Type INNER JOIN
trans_activity ON
B.ln_Last_Trans_Date = trans_activity.Trans_Date AND
B.Loan_Nbr = trans_activity.Acct_Nbr AND
B.Member_Nbr = trans_activity.Member_Nbr
Inner Join Acct_ln_group_tbl
on B.loan_type = acct_ln_group_tbl.loan_type
WHERE (B.ln_Closed = 'Y')
Group by B.loan_type,B.ln_Type_Input_Code, C.lntyp_desc, B.ln_close_date, acct_ln_group_tbl.[Group], b.asof_date
This creates a report that has a list of transactions by Member number and loan number the problem arises that I have multiple transactions for different amounts that I need to sum up on one member number. So, the rows are technically distinct but I need to only report the multiple transactions as one with the amount total for all the transactions.
Any Ideas would be greatly appreciated..
April 20, 2005 at 4:29 pm
Is your goal to have a record like the following?
member_nbr, #loans, sum_of_orig_amt
Do you need the rest of the fields?
Michelle
April 20, 2005 at 4:59 pm
I do need all of the columns in the group statement. I just need one row entry for each of the same member numbers where loan type is the same with the principle paid summed. Some as of dates I will have up to four seperate transactions on the same loan with different amounts. When we attempt to get a count of closed loans during a time range the count is off by the multiple transactions but the payments are in balance.
April 21, 2005 at 1:02 am
You can probably get around it by doing "count(distinct B.Loan_Nbr)" instead of "count(B.loan_type)"
Does this help?
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
April 21, 2005 at 10:37 am
That is getting me closer. I do need to keep the loan type as is because that is what we use for the report. We list all the loans by type that have closed during the date range. That waty we can balance the principle paid to loan type. What I am doing is adding a line for distinct loan numbers that I can group with the loan type and report both numbers to accounting. It all depends on how Crystal Reports reacts when I start updating the T-SQL on it.
Thank you for your help
Dave
April 21, 2005 at 12:56 pm
Well that actually isn't going to work... I think what I need is some kind of IF THEN statement where member number is the same and loan type is the same then return 1 row with the principle sum. Is there any way to get that logic to work in a SP?
April 21, 2005 at 7:03 pm
> I do need to keep the loan type as is because that is what we use for the report.
Yes, but if you want count how many loans there are in that category, then you want count(distinct b.loan_nbr). This doesn't mean you need to group on loan_nbr at all - in fact, if you do, you'll always get '1' in that field.
"count(B.loan_type) as nbr_loans" will always give you then number of records that have a loan_type... which doesn't describe the number of loans at all "count(distinct B.loan_nbr) as nbr_loans".
If this doesn't give you what you're after, can you please try posting some of results of an ungrouped (and thus, unaggregated) query. Then describe what aggregates you're looking for, and we can look to introduce the appopriate ones.
You can talk to my on MSN Msgr if you want... rob_farley at hotmail.
RobF
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply