Solved

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

Posted on 2013-12-02
5
567 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 32

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
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.

688 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