Results 1 to 2 of 2

Thread: Unique Problem

  1. #1
    Join Date
    Dec 2002
    Phoenix, AZ

    Exclamation Unanswered: Unique Problem

    I work for a medical insurance company. I have a list of records that need to be "re-worked". We receive records from hospitals who do not always enter the information correctly. They create multiple claims for one patient visit. I need to assign a unique "Case number" for these claims. I was hoping to write a query that will compare the patient visit dates and if they are close (like with in a day) group them, assigning them a special CASE #. I have included a list of the information below. On a daily basis we receive about 10,000 records that need to be grouped.

    Patient# Claim# patient IN Date Patient OUT Date
    104005715 2624570294 26-Aug-06 26-Aug-06
    104005715 2629772393 27-Aug-06 27-Aug-06
    104005715 2629772392 28-Aug-06 28-Aug-06
    104005715 2628270460 29-Aug-06 29-Aug-06

    104005715 2633111082 14-Nov-06 14-Nov-06
    104005715 2633115375 14-Nov-06 16-Nov-06
    104005715 2706412185 20-Feb-07 20-Feb-07
    104005715 2706412186 21-Feb-07 21-Feb-07
    104006386 2634514189 01-Dec-06 03-Dec-06
    104007675 2718621070 11-Jun-07 11-Jun-07
    104007675 2718623776 13-Jun-07 16-Jun-07

    I have bolded the records that wouuld need a unique Case#. As you can see the hospital has created 4 claims for one patient visit. I need to assign a Case # for this visit, so we can bill them. Any ideas?...
    Last edited by cc3658; 07-16-08 at 12:00.

  2. #2
    Join Date
    Aug 2006
    I'd say that it'd have to start out with a query in your whole DB. This would find similar Patient Numbers. Then based on the patient numbers, you'd look at the date difference to see if the "In Date" and "Out Date" are less than your requirement of 1 or 2 days (whatever you decide). Then with an If/Then or Case statement, you'd say assign a single case number for those records (joining them into one) if not, place them back into the normal pool or status.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts