Need to get the first occurrence of the detail record for based on date.

Hello,

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.

Thanks

Example

Select
 DISTINCT(TX_ID)
     ,TRANS.HSP_ACCOUNT_ID
     ,TRANS.CPT_CODE
     ,TRANS.REVENUE_LOC_ID
     ,TRANS.BUCKET_ID
     ,TRANS.TX_TYPE_HA_C
     ,TRANS.TX_POST_DATE
     ,TRANS.TX_AMOUNT
     ,TRANS.IS_SYSTEM_ADJ_YN
     ,AGING_DETAIL.aging_date
From

TRANS

Inner Join  AGING_DETAIL
     on AGING_DETAIL.hsp_account_id = TRANS.hsp_account_id
     and
     AGING_DETAIL.aging_date = trunc(sysdate,'y')-1

where TRANS.HSP_ACCOUNT_ID = 200040875

order by TX_ID
MIREESEAsked:
Who is Participating?
 
flow01Connect With a Mentor Commented:
you could use a subquery and the rank() function

select TX_ID,
,HSP_ACCOUNT_ID      
,CPT_CODE            
,REVENUE_LOC_ID      
,BUCKET_ID          
,TX_TYPE_HA_C        
,TX_POST_DATE        
,TX_AMOUNT          
,IS_SYSTEM_ADJ_YN    
,aging_date          
from
(select DISTINCT(TX_ID)
     ,TRANS.HSP_ACCOUNT_ID
     ,TRANS.CPT_CODE
     ,TRANS.REVENUE_LOC_ID
     ,TRANS.BUCKET_ID
     ,TRANS.TX_TYPE_HA_C
     ,TRANS.TX_POST_DATE
     ,TRANS.TX_AMOUNT
     ,TRANS.IS_SYSTEM_ADJ_YN
     ,AGING_DETAIL.aging_date
     , rank() over (partition by TRANS.hsp_account_id order by AGING_DETAIL.aging_date) rnk
From TRANS
Inner Join  AGING_DETAIL
     on AGING_DETAIL.hsp_account_id = TRANS.hsp_account_id
     and
     AGING_DETAIL.aging_date >= trunc(sysdate,'y')-1
where TRANS.HSP_ACCOUNT_ID = 200040875
)
where rnk = 1
order by TX_ID
0
 
MIREESEAuthor Commented:
Thanks so much. I have a question. I must iterate through thousands of rows of data. Will this be a huge hit on the database?
0
 
awking00Commented:
The use of distinct will probably cause a bigger hit on the database than the analytic function and it may not be necessary depending on how the results should be partitioned. Perhaps you can provide some sample data and your desired output.
0
 
magarityCommented:
If there isn't one already then an index on the aging_date field may help (I assume you already have indexes on both tables for the hps_account_id). Check the query plan. Either way, the rank functions are the most efficient way to get what you need. If there are so many rows that even the suggested method is not working with proper indexes, that's a whole other problem.

One thing to watch out for is the exact match on two aging dates (down to the second). That will make the rank function put out two records and may mess you up unless you take extra steps.  Check your data for this.
0
 
MIREESEAuthor Commented:
I will look out for performance but it appears to be spot on. Thanks much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.