I have a table called Referral Entry, which records a specific referral of one patient from one physician to another physician. Each record has a MRN value, which indicates the patient number. Since a patient may be referred any number of times, several Referral Entry records will have the same MRN.
In a report, I would like to display the Count of Unique MRNs in a month. Which means that if MRN 12345 had two referrals in January, and 5 referrals in February, it would count as 1 Unique MRN in January, and 1 Unique MRN in February. The report will be grouped by Referring Physician, Service Physician, or Department, all information on the Referral Entry object. Here's the most basic view:
Jan 2011 Unique Count: 4
MRN Date
1 1/5/2011
1 1/5/2011
2 1/7/2011
2 1/7/2011
3 1/10/2011
4 1/15/2011
Feb 2011 Unique Count: 3
MRN Date
1 2/5/2011
1 2/5/2011
2 2/9/2011
3 2/15/2011
3 2/15/2011
One reason for using a report is that the dataset is pretty large. In some cases, we may need summary totals on 2 million records in one display table. Since we need to be able to summarize based on several criteria, it's impractical to use summary database records. If you have any other ideas on how we could create this data view, please throw them out there. Thanks in advance!
Edit: The MRN field is a text field on Referral Entry. For the purposes of this question, Referral Entry is essentially a standalone object.