Check if there are any duplicate claims paid in a year for each member

you'll have to use a group by and a self join
group by to get the max, and then self join to display the information not in the group by

with latest_claims as (
  SELECT MEMBER_ID, program, max(claim_id) mclaim_id
  from yourtable
  where LOW_SRV_DT > sysdate - 366
  group by MEMBER_ID, program)
select yt.* 
from yourtable yt, latest_claims lc
where yt.member_id = lc.member_id 
  and yt.program = lc.program

Select allOpen in new window

Hi Gruwez,

Thanks for your reply.

I have a question you have this where condition in the above query that says LOW_SRV_DT > sysdate - 366 . Does it checks if there are any duplicate claims within an yr of Low_srvc_dt + 366.. Does it needs to be Less than or greater than ?


Thanks,
Raj

What are you trying to do ?

Prevent duplicates ?
Or check for existing duplicates

You should be preventing if you dont want duplicates

Thanks for the reply.

The data is already loaded.. we need to check if there are any duplicates and pull the latest claim as exception.

Thanks

Add the having count(*) > 1 to get the dupes

with latest_claims as (
  SELECT MEMBER_ID, program, max(claim_id) mclaim_id
  from yourtable
  where LOW_SRV_DT > sysdate - 366
  group by MEMBER_ID, program
  having count(*) >1 )
select yt.*
from yourtable yt, latest_claims lc
where yt.member_id = lc.member_id
  and yt.program = lc.program

Year match gets tricky.   subtracting a specific number is problematic unless your company has defined a year to mean X days.

We all have 12 months so subtracting 12 months from today is almost always one year.  In Oracle dates also have a time piece built in.  So does sysdate.   To remove the time piece, use trunc.

12 months from now is:  add_months(trunc(sysdate),-12)

Thanks Gruwez and slightwv.

 Sorry for asking again.  Could you please explain why we need to have  LOW_SRV_DT > sysdate - 366 or add_months(trunc(sysdate),-12) .  

I want to know if there are any duplicate submissions from the Start date of LOW_SRV_DT +365.  If there are any then show those duplicate submissions

Does this query do the job.

Could you please help me understand.

I think we might have misunderstood the requirement.  We went from the provided day and one year back.  If that isn't what you need, just make the examples a '+' instead of a '-'.

You can add one year to a date with add_months(trunc(some_date_value),+12) .  

If you can provide some sample data and expected results, we can probably provide some tested SQL.

MEMBER           CLAIM ID        M_RULE                           PROGRAM START DATE             PROGRAM END DATE
   
   1                           1                         1                                 1/1/2017 (low_SRVC_DT)           1/1/2017  

   1                       2                         1                                           6/1/2017  

We should not see two claims submitted in a year for M_rule 1 for a member.  We need to validate/check for the duplicate claim for eg. The 2nd record i.e The claim Id 2 should be caught as the exception record since its duplicated for the same member in a year for M_rule 1.'

shouldn't your program end date be 1/1/2018 ?

Sorry for late reply. It was a hectic day in office.  
Yes Gruwez it should be 1/1/2018. Member ID and Claim ID are unique.

 Program / M_rule is same. Sorry for the confusion. I will check the query and let you know.

My question is Where does it pull  the exception record or the duplicate record. You haven' t  mentioned in the Select statement. Select statement should have latest claims right ?

Also I didn't understood why we need to self join?

Please reply back so that I can close it.

Thanks,
Jai

 Thanks for your prompt reply.

well, the having count(*) > 1 will extract the duplicates

and when you self join that query with the original table, then you get all the records of the table

before deleting records it's always wise to run a visual check first
to see if the results are what you expect

Thanks for the reply.

Ok what if I just want the query that extracts just the duplicates. I just want to see the duplicates.

Thanks

Can we use row_number over partition by in PL SQL to only catch the exception claim id's. I am sorry if i have explained it simply. There are 4 tables involved. So it is difficult to join as mentioned above.  We want to pull exception records and the related values.

Thanks for your suggestions. Please reply.

>>Can we use row_number over partition by in PL SQL to only catch the exception claim id'

Probably.

Also, PL/SQL is Oracle's Procedural Language.  You probably don't need it.  SQL is just SQL.

If you can provide some sample data and expected results we can probably provide working and tested SQL.

Hi Guys, Sorry for the late reply. I was down with viral fever.

Following is the expected results for the query should be.
So if you could  see below there are 4 duplicate milestones for Milestone 1 for different dates in a year.



In the below result set, We need to catch 3 exception claims that were paid except the one that is dated for 1/1/2017 . So we need to catch the exceptions that is for dates 6/7/2017 , 8/7/2017, 9/18/2017 in the query along with the claim id and member and milestone.  We need to fetch those exceptions fall in the one yr from the start of first milestone

This query is run for the previous month. so we need to find out for each member what is the first claim for that member in a year and if the claim for that month is a duplicate then catch that as a duplicate claim.  So if we are running the query in october for the month of sep claims, we need to check the starting date of the milestone 1 for the member A which is 1/1/2017 and then from there on we need to check if there are any claims submitted after 1/1/2017. If there are any catch those exception claims in a year.

MEMBER     CLAIM ID                  MILESTONE                         LOW_SRVC_DT                         

A                     123                                   1                                                1/1/2017                  
A                     124                               1                                               6/7/2017
A                     132                                    1                                               8/7/2017            
A                      321                               1                                                9/18/2017
A                    431                                  1                                                3/01/2018



so the final output should look like this :

MEMBER     CLAIM ID                  MILESTONE                             LOW_SRVC_DT       
A                     124                               1                                               6/7/2017
A                         132                                1                                               8/7/2017            
A                          321                               1                                               9/18/2017

we should not catch the claim that falls after one yr.

Hope this helps
Appreciate your time on this.


Thanks,
Sridhar

Hi Netminder,
That was so fast. Thanks for your reply. I will test it if it is returning the expected result set

I have few questions.
1st  question Can you please explain what is low_srvc_dt-min(low_srvc_dt). Is it min of lo_srvc_dt ?
2nd question .. what is days_between  .. is it a date function.

There is one more requirement i forgot to add. There is this Error description field we need to add in the query.
So it should say if there are duplicate claims, it should specify "This claim id is a duplicate claim" .


MEMBER     CLAIM ID                  MILESTONE                             LOW_SRVC_DT       Error Desc
A                     124                               1                                               6/7/2017             Its a duplicate claim
A                         132                                1                                         8/7/2017            Its a duplicate claim
A                          321                               1                                         9/18/2017         It's a duplicate claim

Thanks for your time.

Jai

>>1st  question Can you please explain what is low_srvc_dt-min(low_srvc_dt). Is it min of lo_srvc_dt ?

min(low_srvc_dt) over(partition by member_id, milestone) is the minimum date for the grouping of member_id and milestone.

subtracting two dates in Oracle produces the number of days between the dates.

For your sample the minimum date is 1/1/2017 so I take every date and subtract it to get the days in between.


>> 2nd question .. what is days_between  .. is it a date function.

That is the alias I gave to the column in the inner query.

>>There is this Error description field we need to add in the query.

OK, add it.

To see everything in action, select the fields individually:

select member_id,
	claim_id,
	milestone,
	low_srvc_dt,
	min(low_srvc_dt) over(partition by member_id, milestone) min_date,
	low_srvc_dt-min(low_srvc_dt) over(partition by member_id, milestone) days_between
from tab1
/

Select allOpen in new window

LO_SRV_DT is the date field that says when each milestone has started.  

Hope fully this query works.  I will check and let you know if that works.


Thanks,
JAI

I see all the records for that member using above query. It doesn't eliminate the first claim for that member in the year.

I only want to see whatever duplicate claims were submitted in a year not the first one based on LO_SRV_DT for that member. Except the first claim, i want to see all the other duplicates for that particular member in the result set.

How can I catch only those duplicates leaving the first claim for that member?


MEMBER     CLAIM ID                  MILESTONE                         LOW_SRVC_DT                        

A                     123                                   1                                                1/1/2017                  
A                     124                               1                                               6/7/2017
A                     132                                    1                                               8/7/2017            
A                      321                               1                                                9/18/2017

I want to catch last 3  records for that member starting from 6/7/2017 to 9/18/2017.

Appreciate your help.

Thanks,
Jai

>>I see all the records for that member using above query.

Which above query?

If it was the one where I posted in #a41934138 , then yes, you get them all.  That was just an example to help explain what everything is doing because you had questions about the other query.

The one in #a41933922 should work.  Does it not work?

Thanks Netminder for helping me out . Response was very quick. Thanks Geert as well for earlier response.