> there are different claim numbers that have tell tale fields in common namely the service date
>field is the same , the member number is alike and the diagnosis code matches on 2 or more different claim numbers.

>...tell tale fields in common namely the service date
>field is the same , the member number is alike and the diagnosis code matches on 2 or more different claim numbers.

Joe, I've done quite a bit of claims analysis with VFP i n the past. Yours is an interesting problem because the ultimate decision to deny payment is likely to be most accurate if left up to a human (rather than software), yet the right software can make a human 100x more efficient at finding those claims that might be fraudulent.

You mention that the diagnosis code (ICD-9) matches on 2 or more claims. What about CPT-4 codes (i.e., the interventions that were performed to treat the diagnoses)? Do those match too?. Some claims processing systems allow up to 4 ICD9 codes per line item, although most users of such systems only use two (occasionally 3) of the those 4 fields. Are you saying that all ICD9 codes and the CPT code are the same when comparing two different claims? What about first and last dates of service? Identical here, too?

Are you working with all claims? Or are you analyzing only paid claims?

Another point that might be helpful to keep in mind: Often, a provider (of which I am one in another life), will re-file the same claim for non-fraudulent reasons, among them: (1)The provider found out that something was clerically amiss in their initial submission (a sometimes-overused excuse for the payor to deny payment). Upon contacting the payor, the provider was instructed to re-submit the claim. (2) The provider waited a "long time" and received no payment, and the payor could not verify that the claim had ever been received.

Often, it is not the provider at fault, but rather an inefficient and inaccurate claims-processing infrastructure that generates lots of "claims noise". If you want to see how bad it can get, look at what happened to Oxford Health Plans last October.

At any rate, as already suggested, start by finding those claims that appear to occur more than once:

Select Count(*) as HowMany, MemberID, FirstDateOfService, LastDateOfService, All_codes_Of_Interest From tblClaims ;
Into Cursor Work1 ;
Group by MemberID, FirstDateOfService, LastDateOfService, All_codes_Of_Interest ;
Having HowMany > 1

* (Note that All_codes_Of_Interest can be more than one field. Adjust the query accordingly)

* Next step:
Select * From tblClaims Into Cursor Dupes ;
Where Exists (Select * From Work1 where Work1.MemberID = Dupes.MemberID) ;
Order by MemberID, FirstDateOfService

Now the cursor Dupes contains all data from those line items that might really be duplicates. The real value of this analyis is delivered by putting a nice presentation layer on top of this table for your claims experts to use, or perhaps export the results to an Excel spreadsheet.