Latest Date

Database
N/A

Latest Date

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.
4 REPLIES
N/A

Re: Latest Date

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;
N/A

Re: Latest Date

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;
N/A

Re: Latest Date

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.
N/A

Re: Latest Date

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;