Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-12-02
5
Medium Priority
?
570 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo 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…

604 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