|
|
|
Forum Member
 
Group: Forum Members
Last Login: Yesterday @ 1:21:33 AM
Posts: 28,
Visits: 147
|
|
Hi
I have created the query below to retrieve the maximum date in order to get the outstanding limit, but the query return more than one dates for the same account number,
and I only one to see the last date the transaction was made or processed, which is my maximum date.
select
d.account_number
,d.account_modification_number
,d.account_open_date
,max(account_summary_date) as Max_Date
, (credit_limit_amount - balance_amount) as outstanding_limit
from ince.balances d
inner join ince.credit_limit c
on d.account_number = c.account_number
and d.account_modification_number = d.account_modification_num
and d.sb_account_open_dt = c.sb_account_open_dt
group by 1,2,3;
Please help.
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/15/2008 11:52:29 AM
Posts: 9,
Visits: 17
|
|
Try this:
select * from
(select
d.account_number
,d.account_modification_number
,d.account_open_date
,account_summary_date as Max_Date
, (credit_limit_amount - balance_amount) as outstanding_limit
, rank() over (order by account_summary_date desc) rowrank
from ince.balances d
inner join ince.credit_limit c
on d.account_number = c.account_number
and d.account_modification_number = d.account_modification_num
and d.sb_account_open_dt = c.sb_account_open_dt
group by 1,2,3,4) a
where rowrank=1;
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 1:21:33 AM
Posts: 28,
Visits: 147
|
|
Hi Dales
I have tried using the option you suggested below, the query runs forever, this table ACCT_BAL_SUMMARY_DD has so many records,
would be another suggestion to help be resolve this problem, and what is the main function of rowrank = 1
select * from
(select
d.account_num
,d.account_modifier_num
,d.sb_account_open_dt
,account_summary_dt as Max_Date
, (credit_limit_amt - Ending_Ledger_Bal_Categ_Amt) as outstanding_limit
, rank() over (order by account_summary_dt desc) rowrank
from testeiw.ACCT_BAL_SUMMARY_DD d
inner join testeiw.account_credit_limit c
on d.account_num = c.account_num
and d.account_modifier_num = c.account_modifier_num
and d.sb_account_open_dt = c.sb_account_open_dt
and c.Credit_Limit_End_Dt IS NULL
and d.Balance_Category_Type_Cd = 1 and d.Account_Modifier_Num =10
group by 1,2,3,4,5) a
where rowrank=1;
Ince Chauke
Jnr BI System Analyst
Data Warehouse Department
Knowledge Leads The World.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 8/15/2008 11:52:29 AM
Posts: 9,
Visits: 17
|
|
The main function of the rowrank=1 is to give you the most recent account_summary_dt.
As for performance, I don't know what to tell you.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/14/2008 11:48:31 AM
Posts: 1,
Visits: 3
|
|
Hi,
Try this for a better performance:
select * from
(select
d.account_number
,d.account_modification_number
,d.account_open_date
,max(account_summary_date) as Max_Date
, (credit_limit_amount - balance_amount) as outstanding_limit
, rank() over (order by account_summary_date desc) rowrank
from ince.balances d
inner join ince.credit_limit c
on d.account_number = c.account_number
and d.account_modification_number = d.account_modification_num
and d.sb_account_open_dt = c.sb_account_open_dt
group by 1,2,3) a
where rowrank=1;
Gil
|
|
|
|