SQL query for MS SQL Server - Find similar records in a time period

I have a database with medical/dental claims in it.  I'm trying to create a SQL query that wil look at all the claims in the past week (for example) and then find any claims in the past 6 months (also for example)  that might potentially be a duplicate.  By duplicate I mean that the member ID is the same, the procedure code is the same, the provider (doctor) is the same and the claim dollar amount is the same.

I'm having trouble with this and am ending up creating an infinite loop of sorts where I get every claim joined to every other claim.

Any guidance on this would be greatly appreciated...

Basically the table I'm querying has columns (that I'm interested in of the form):

Date_of_service, member_id, claim_number, provider_id, diagnosis_code, claim_amount
LVL 32
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


select distinct mytable.member_id,mytable.claim_number,mytable.Date_of_service, mytable.claim_amount, mytable.diagnosis_code from mytable inner join
(select distinct member_id,claim_amount, diagnosis_code from mytable where Date_of_service >= '12/1/2007' and Date_of_service<='12/8/2007') as new
 on mytable.member_id = new.member_id and mytable.claim_amount = new.claim_amount and mytable.diagnosis_code = new.diagnosis_code
where mytable.Date_of_service =>'1/1/2007' and mytable.Date_of_service <='12/8/2007' order by mytable.member_id, mytable.diagnosis_code, mytable.Date_of_service

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jhanceAuthor Commented:
Thanks for the prompt reply.  I'll give this a try and let you know.
jhanceAuthor Commented:
I think it's close but I had some other issues come up and I haven't been able to complete this task yet.  I hope to be able to get back to it in the next week or so...  But rather than let this question hang, I'll go ahead and accept/close it and will post any further information or questions...

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.