Need to get the first occurrence of the detail record for based on date.
Posted on 2013-12-02
I would like to only return the first occurrence of record. He is the setup.
Trans tables contains many transactions.
AGING_DETAIL contains several records based on the last day of the month.
I would like to only return transactions based on the first occurrence of the AGING_DETAIL record where the AGING_DETAIL date is equal to or greater than the last day of the previous year.
The current logic only returns records based on the last day of the previous year and if I use >=, I get duplicates.
Inner Join AGING_DETAIL
on AGING_DETAIL.hsp_account_id = TRANS.hsp_account_id
AGING_DETAIL.aging_date = trunc(sysdate,'y')-1
where TRANS.HSP_ACCOUNT_ID = 200040875
order by TX_ID