[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 572
  • Last Modified:

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
0
jhance
Asked:
jhance
  • 2
  • 2
1 Solution
 
gigglickCommented:

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
0
 
jhanceAuthor Commented:
Thanks for the prompt reply.  I'll give this a try and let you know.
0
 
gigglickCommented:
work?
0
 
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...

Thanks...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now