AnsweredAssumed Answered

Help with a massive medical database problem

Question asked by dsimonson on Jul 1, 2012
Latest reply on Jul 2, 2012 by philmodjunk


Help with a massive medical database problem


<!--?xml version="1.0" encoding="UTF-8" standalone="no"?-->I am trying to figure out a script that will help me summarize a massive amount of data.  Any tips would be appreciated.  

The data consists of records regarding individual patient visits exported from a medical management program.  Each record contains (among other fileds) a doctor's initials, the cpt code for the visit, the charge, and the month of the visit.
I have over 400,000 such records, recording the visits to about 20 doctors over the past 5 years.  There are 21 different CPT codes involved.  
I have approached this with brute force scripting, and it is taking way too long.  I am hoping someone out there will be able to show me how to do it more efficiently.  What I have done so far is write a looping script that increments the month, year, dr initials, and cpt code, finds the records, writes their summary fields to variables, creates a new record in a summary table, updates the fields in the summary table with the stuff from the variables, and then loops through again.   I am doing this for all 20 doctors and all 21 cpt codes each month.  As you can imagine, it is painfully slow.  
It would be so much easier if I could figure out a way to get all the records for one doctor for one month, and then use the summary function somehow to put the data into an array or something.
Any hints or help would be greatly appreciated.