Maximum Date

UDA
Enthusiast

Maximum 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
Enthusiast

Re: Maximum Date

Can you post the tables DDL?
Enthusiast

Re: Maximum Date

CREATE MULTISET TABLE ince.balances ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
FREESPACE = 0 PERCENT,
CHECKSUM = DEFAULT
(
Account_Number DECIMAL(18,0) NOT NULL,
Account_Modification_Number SMALLINT COMPRESS (1 ,10 ,12 ),
Account_Open_Date DATE FORMAT 'YYYY-MM-DD' COMPRESS (DATE '2006-06-08',DATE '2006-11-20',DATE '2006-06-09',DATE '2006-11-21',DATE '2006-11-22',DATE '2006-06-12',DATE '2006-11-24',DATE '2006-06-14',DATE '2006-06-15',DATE '2006-11-27',DATE '2006-11-28',DATE '2006-11-29',DATE '2006-11-30',DATE '2006-06-19',DATE '2006-06-20',DATE '2006-06-22',DATE '2006-06-23',DATE '2006-01-13',DATE '2006-06-26',DATE '2006-06-27',DATE '2006-01-16',DATE '2006-06-28',DATE '2006-01-17',DATE '2006-06-29',DATE '2006-01-18',DATE '2006-06-30',DATE '2006-01-20',DATE '2007-01-08',DATE '2006-01-25',DATE '2007-01-09',DATE '2006-01-26',DATE '2006-01-27',DATE '2005-09-12',DATE '2007-01-12',DATE '2006-01-30',DATE '2006-01-31',DATE '2007-01-15',DATE '2007-01-16',DATE '2006-09-01',DATE '2007-01-17',DATE '2007-01-19',DATE '2006-09-04',DATE '2006-09-05',DATE '2006-09-06',DATE '2007-01-22',DATE '2006-09-07',DATE '2006-09-08',DATE '2007-01-24',DATE '2007-01-25',DATE '2007-01-26',DATE '2006-09-11',DATE '2006-09-12',DATE '2005-09-29',DATE '2006-09-13',DATE '2007-01-29',DATE '2007-01-30',DATE '2006-04-03',DATE '2006-09-15',DATE '2007-01-31',DATE '2006-04-04',DATE '2006-04-05',DATE '2006-04-06',DATE '2006-09-18',DATE '2006-09-19',DATE '2006-09-20',DATE '2006-04-10',DATE '2006-09-22',DATE '2006-04-13',DATE '2006-09-26',DATE '2006-09-27',DATE '2006-09-28',DATE '2006-09-29',DATE '2006-04-19',DATE '2006-04-20',DATE '2005-12-05',DATE '2006-04-21',DATE '2005-12-06',DATE '2005-12-07',DATE '2006-04-24',DATE '2006-04-25',DATE '2006-04-26',DATE '2006-04-28',DATE '2006-12-05',DATE '2006-12-06',DATE '2005-02-07',DATE '2006-07-03',DATE '2006-07-04',DATE '2006-07-05',DATE '2006-07-06',DATE '2006-07-07',DATE '1980-01-01',DATE '2006-07-10',DATE '2006-07-11',DATE '2006-07-12',DATE '2006-02-01',DATE '2006-07-13',DATE '2006-02-02',DATE '2006-07-14',DATE '2006-02-03',DATE '2006-07-17',DATE '2006-02-06',DATE '2006-07-18',DATE '2006-02-07',DATE '2006-07-19',DATE '2006-02-08',DATE '2006-07-20',DATE '2006-02-09',DATE '2006-07-21',DATE '2006-02-10',DATE '2006-07-24',DATE '2006-02-13',DATE '2006-07-25',DATE '2006-02-14',DATE '2006-07-26',DATE '2006-02-15',DATE '2006-07-27',DATE '2006-02-16',DATE '2006-07-28',DATE '2006-02-17',DATE '2007-02-01',DATE '2007-02-02',DATE '2006-07-31',DATE '2006-02-20',DATE '2006-02-21',DATE '2007-02-05',DATE '2006-02-22',DATE '2007-02-06',DATE '2006-02-23',DATE '2007-02-07',DATE '2006-02-24',DATE '2007-02-08',DATE '2007-02-09',DATE '2006-02-27',DATE '2006-02-28',DATE '2007-02-12',DATE '2007-02-13',DATE '2007-02-16',DATE '2005-10-17',DATE '2006-10-02',DATE '2006-10-03',DATE '2006-10-04',DATE '2006-10-05',DATE '2006-10-06',DATE '2007-02-23',DATE '2006-10-09',DATE '2006-10-10',DATE '2007-02-26',DATE '2005-10-27',DATE '2006-10-11',DATE '2007-02-27',DATE '2005-10-28',DATE '2006-10-12',DATE '2007-02-28',DATE '2006-10-13',DATE '2006-05-02',DATE '2005-10-31',DATE '2006-05-03',DATE '2006-05-04',DATE '2006-10-16',DATE '2006-05-05',DATE '2006-10-17',DATE '2006-10-18',DATE '2006-10-19',DATE '2006-05-08',DATE '2006-10-20',DATE '2006-05-10',DATE '2006-05-11',DATE '2006-10-23',DATE '2006-10-24',DATE '2006-10-25',DATE '2006-10-26',DATE '2006-05-15',DATE '2006-10-27',DATE '2006-05-16',DATE '2006-05-17',DATE '2006-05-18',DATE '2006-10-30',DATE '2006-10-31',DATE '2006-05-22',DATE '2006-05-23',DATE '2006-05-25',DATE '2006-05-26',DATE '2006-05-29',DATE '2006-05-30',DATE '2006-05-31',DATE '2006-08-01',DATE '2006-08-02',DATE '2006-08-03',DATE '2006-08-04',DATE '2006-08-07',DATE '2006-08-08',DATE '2006-08-10',DATE '2006-08-11',DATE '2006-03-02',DATE '2006-08-14',DATE '2006-03-03',DATE '2006-08-15',DATE '2006-08-16',DATE '2006-08-17',DATE '2006-03-06',DATE '2006-08-18',DATE '2006-03-07',DATE '2006-03-08',DATE '2006-03-09',DATE '2006-08-21',DATE '2006-03-10',DATE '2006-08-22',DATE '2006-08-23',DATE '2006-08-24',DATE '2006-03-13',DATE '2006-08-25',DATE '2006-03-14',DATE '2006-03-15',DATE '2006-03-16',DATE '2006-08-28',DATE '2005-11-01',DATE '2006-08-29',DATE '2007-03-01',DATE '2005-11-02',DATE '2006-08-30',DATE '2007-03-02',DATE '2006-08-31',DATE '2006-03-20',DATE '2006-03-22',DATE '2007-03-07',DATE '2005-11-08',DATE '2006-03-24',DATE '2005-11-09',DATE '2006-03-27',DATE '2006-03-28',DATE '2006-03-29',DATE '2005-11-14',DATE '2005-11-15',DATE '2006-03-31',DATE '2005-11-16',DATE '2006-11-01',DATE '2006-11-02',DATE '2006-11-03',DATE '2006-11-06',DATE '2005-11-23',DATE '2006-11-07',DATE '2005-11-24',DATE '2006-11-08',DATE '2006-11-09',DATE '2006-11-10',DATE '2005-11-29',DATE '2006-06-01',DATE '2005-11-30',DATE '2006-11-15',DATE '2006-11-16',DATE '2006-06-05',DATE '2006-11-17',DATE '2006-06-06',DATE '2006-06-07'),
Balance_Type_Code SMALLINT COMPRESS (1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ,11 ,12 ,13 ,14 ),
Account_Summary_Date DATE FORMAT 'YYYY-MM-DD',
Balance_Amount DECIMAL(18,2) COMPRESS (0.00 ,8000.00 ,0.01 ,0.02 ,0.03 ,100.00 ,5000.00 ,2000.00 ,10000.00 ,20000.00 ,50.00 ,1000.00 ,6000.00 ,20.00 ,3000.00 ),
Source_System_Code SMALLINT NOT NULL COMPRESS (1 ,10 ,12 ),
PRIMARY INDEX ( Account_Number )
PARTITION BY RANGE_N(Account_Summary_Date BETWEEN DATE '2006-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH ,
DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH ,
DATE '2008-01-01' AND DATE '2008-12-31' EACH INTERVAL '1' MONTH ,
NO RANGE OR UNKNOWN);
Enthusiast

Re: Maximum Date

Table 2 Credit_Limit DDL

CREATE MULTISET TABLE ince.CREDIT_LIMIT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
FREESPACE = 0 PERCENT,
CHECKSUM = DEFAULT
(
Account_Number DECIMAL(18,0) NOT NULL,
Account_Modification_Number SMALLINT COMPRESS (1 ,10 ),
Account_Open_Date DATE FORMAT 'YYYY-MM-DD' COMPRESS (DATE '2006-06-08',DATE '2006-06-09',DATE '2006-11-21',DATE '2006-11-22',DATE '2006-06-12',DATE '2006-06-14',DATE '2006-11-27',DATE '2006-11-29',DATE '2006-11-30',DATE '2006-06-20',DATE '2006-01-10',DATE '2006-06-22',DATE '2006-06-23',DATE '2006-01-13',DATE '2006-06-26',DATE '2006-06-27',DATE '2006-01-16',DATE '2006-06-28',DATE '2006-01-17',DATE '2006-06-29',DATE '2006-01-18',DATE '2006-06-30',DATE '2006-01-19',DATE '2007-06-15',DATE '2006-01-20',DATE '2006-01-25',DATE '2006-01-26',DATE '2005-09-12',DATE '2005-09-15',DATE '2006-01-31',DATE '2005-09-16',DATE '2005-09-19',DATE '2005-09-21',DATE '2006-09-05',DATE '2006-09-06',DATE '2005-09-23',DATE '2006-09-07',DATE '2006-09-08',DATE '2005-09-29',DATE '2006-09-13',DATE '2007-01-29',DATE '2005-09-30',DATE '2007-01-30',DATE '2006-09-15',DATE '2007-01-31',DATE '2006-04-04',DATE '2006-04-05',DATE '2006-04-06',DATE '2006-09-20',DATE '2006-04-10',DATE '2006-04-11',DATE '2006-04-13',DATE '2006-09-27',DATE '2005-12-01',DATE '2006-04-18',DATE '2007-09-14',DATE '2006-04-19',DATE '2006-04-20',DATE '2007-04-04',DATE '2005-12-05',DATE '2006-04-21',DATE '2007-04-05',DATE '2005-12-06',DATE '2005-12-07',DATE '2006-04-24',DATE '2005-12-09',DATE '2006-04-25',DATE '2006-04-26',DATE '2007-04-11',DATE '2005-12-12',DATE '2007-04-12',DATE '2007-04-13',DATE '2005-12-14',DATE '2007-09-27',DATE '2007-04-16',DATE '2007-04-17',DATE '2007-04-18',DATE '2007-04-20',DATE '2007-04-26',DATE '2006-07-04',DATE '2006-07-05',DATE '2006-07-07',DATE '1980-01-01',DATE '2006-02-01',DATE '2006-02-02',DATE '2006-02-03',DATE '1975-07-01',DATE '2006-02-06',DATE '2006-02-07',DATE '2006-07-19',DATE '2006-02-08',DATE '2006-02-09',DATE '2006-02-10',DATE '2006-02-13',DATE '2006-07-25',DATE '2006-02-14',DATE '2006-07-26',DATE '2006-02-15',DATE '2006-07-27',DATE '2006-02-16',DATE '2006-07-28',DATE '2006-02-17',DATE '2007-02-01',DATE '2007-02-02',DATE '2006-07-31',DATE '2006-02-20',DATE '2006-02-21',DATE '2007-02-05',DATE '2005-10-06',DATE '2006-02-22',DATE '2007-02-06',DATE '2005-10-07',DATE '2006-02-23',DATE '2007-02-07',DATE '2006-02-24',DATE '2007-02-08',DATE '2007-02-09',DATE '2005-10-10',DATE '2005-10-11',DATE '2006-02-27',DATE '2005-10-12',DATE '2006-02-28',DATE '2005-10-13',DATE '2007-02-13',DATE '2005-10-14',DATE '2007-02-14',DATE '2007-02-15',DATE '2005-10-17',DATE '2005-10-18',DATE '2006-10-02',DATE '2005-10-19',DATE '2006-10-03',DATE '2005-10-20',DATE '2006-10-04',DATE '2005-10-21',DATE '2006-10-05',DATE '2006-10-06',DATE '2007-02-23',DATE '2005-10-24',DATE '2005-10-25',DATE '2005-10-26',DATE '2007-02-26',DATE '2005-10-27',DATE '2006-10-11',DATE '2007-02-27',DATE '2005-10-28',DATE '2007-02-28',DATE '2006-10-13',DATE '2005-10-31',DATE '2006-05-03',DATE '2006-05-04',DATE '2006-05-05',DATE '2006-10-17',DATE '2007-10-03',DATE '2006-10-20',DATE '2007-10-05',DATE '2006-05-10',DATE '2006-05-11',DATE '2007-10-10',DATE '2006-05-15',DATE '2006-05-16',DATE '2006-05-17',DATE '2006-05-18',DATE '2006-10-31',DATE '2006-05-22',DATE '2006-05-23',DATE '2007-05-08',DATE '2006-05-25',DATE '2006-05-26',DATE '2007-05-10',DATE '2006-05-30',DATE '2007-05-14',DATE '2007-10-26',DATE '2006-05-31',DATE '2007-10-30',DATE '2007-10-31',DATE '2006-08-02',DATE '2006-08-03',DATE '2007-05-31',DATE '2006-08-04',DATE '2006-08-07',DATE '2006-08-08',DATE '2006-08-10',DATE '2006-08-11',DATE '2006-03-03',DATE '2006-08-15',DATE '2006-08-16',DATE '2006-08-17',DATE '2007-08-01',DATE '2006-03-06',DATE '2006-08-18',DATE '2006-03-07',DATE '2006-03-08',DATE '2006-03-09',DATE '2006-03-10',DATE '2006-08-22',DATE '2006-08-23',DATE '2007-08-07',DATE '2007-08-08',DATE '2006-03-13',DATE '2006-03-14',DATE '2006-03-15',DATE '2006-03-16',DATE '2005-11-01',DATE '2007-03-01',DATE '2005-11-02',DATE '2007-03-02',DATE '2005-11-03',DATE '2005-11-04',DATE '2007-08-16',DATE '2007-08-17',DATE '2007-03-06',DATE '2005-11-07',DATE '2007-03-07',DATE '2005-11-08',DATE '2006-03-24',DATE '2007-03-08',DATE '2005-11-09',DATE '2007-03-09',DATE '2005-11-10',DATE '2006-03-28',DATE '2007-08-24',DATE '2006-03-29',DATE '2007-03-13',DATE '2005-11-14',DATE '2007-03-14',DATE '2005-11-15',DATE '2006-03-31',DATE '2007-03-15',DATE '2005-11-16',DATE '2007-03-16',DATE '2005-11-17',DATE '2007-08-29',DATE '2005-11-18',DATE '2006-11-02',DATE '2007-08-30',DATE '2006-11-03',DATE '2007-08-31',DATE '2005-11-21',DATE '2005-11-23',DATE '2006-11-07',DATE '2005-11-24',DATE '2006-11-08',DATE '2006-11-09',DATE '2006-11-10',DATE '2005-11-28',DATE '2005-11-29',DATE '2006-06-01',DATE '2007-03-29',DATE '2005-11-30',DATE '2007-03-30',DATE '2006-11-15',DATE '2006-11-16',DATE '2006-06-06',DATE '2006-06-07'),
Limit_Type_Code SMALLINT COMPRESS (1 ,4 ,5 ,6 ,7 ,8 ),
Credit_Limit_Start_Date DATE FORMAT 'YYYY-MM-DD',
Source_System_Cd SMALLINT NOT NULL COMPRESS (1 ,10 ),
Credit_Limit_Amount DECIMAL(18,2),
Credit_Limit_End_Datet DATE FORMAT 'YYYY-MM-DD' COMPRESS ,
PRIMARY INDEX ( Account_Number )
PARTITION BY RANGE_N(Credit_Limit_Start_Date BETWEEN DATE '2006-01-01' AND DATE '2006-12-31' EACH INTERVAL '1' MONTH ,
DATE '2007-01-01' AND DATE '2007-12-31' EACH INTERVAL '1' MONTH ,
DATE '2008-01-01' AND DATE '2008-12-31' EACH INTERVAL '1' MONTH ,
NO RANGE OR UNKNOWN);
Fan

Re: Maximum Date

Hi,

You were not doing a sum for outstanding_limit, account_number wise. Could u try this out:

select
d.account_number
,d.account_modification_number
,d.account_open_date
,max(account_summary_date) as Max_Date
, sum(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;