Solved

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

Posted on 2013-12-02
5
563 Views
Last Modified: 2013-12-03
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
0
Comment
Question by:MIREESE
5 Comments
 
LVL 20

Accepted Solution

by:
flow01 earned 500 total points
ID: 39690888
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
 

Author Comment

by:MIREESE
ID: 39690974
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
 
LVL 31

Expert Comment

by:awking00
ID: 39691022
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
 
LVL 13

Expert Comment

by:magarity
ID: 39691170
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
 

Author Closing Comment

by:MIREESE
ID: 39693138
I will look out for performance but it appears to be spot on. Thanks much!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now