Solved

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

Posted on 2013-12-02
5
566 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

749 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