Formula to pick duplicates

[shelby55]

Hello

I am using Crystal 9 reporting from an SQL database.

In ER Department physicians "pick" a patient electronically which date/time stamps their physician initial assessment time (PIA). However, many doctors are picking a couple of patients at once which is skewing our data.

How can I write a formula to flag when the same date/time for the same physician is found? Thanks.

 

[fisheromacse]

One possibility would be to group your report on Physican (PhysicianID) and use an array to build a 'field' containing all the PIA's before the current record, then compare the current PIA to the contents of the built 'field', returning a True/False (in this case i used "DUPE" and "" [blank]) type value.

Place this in the details section:
//{@accumulatedata}
whileprintingrecords;
stringvar PIAs := PIAs + chr(13) + {Table.PIA};
PIAs

Place this in the group header:
//{@accumulatereset}
whileprintingrecords;
stringvar PIAs := ""


Place this in the details section:
//{@accumulateEval}
numbervar TF;
stringvar PIAs;
IF isnull({Table.PIA}) or TRIM(totext({Table.PIA}))=""
THEN ""
else
IF {Table.PIA} in PIAs then "DUPE" else ""


This should check the current PIA value against all the ones before it for a physician.

 

[lbass]

Insert a group on physician and then a group on the datetime field (on change of the minute), and then go to report->selection formula->GROUP and enter:

count({table.datetime},{table.datetime}, "by Minute") > 1

-LB

 

[shelby55]

Hi

Thanks for the quick response. This is a manual running total, correct? If so, then 2 formula shouldn't be in details, should it?

 

[shelby55]

Hi LB

Sorry but I didn't see your reply when I answered last. I need a formula to be able to sum how many duplicates within the period chosen for the report (which will always be monthly).

Thanks.

 

[lbass]

Then use the group selection and add a running total that does a count of {table.servicedate}, evaluate on change of group {table.servicedate}, reset on change of physician (if per physician) or never if at the grand total level. This assumes that you want to count each datetime that has a duplicate once. If you want to count the total records that are part of sets of duplicates, then evaluate for each record.

-LB

 

[shelby55]

Hi LB

Sorry but I don't understand what you're advising. So I have two groups - physician date/time and physician? The running total will be on the physician date time count and on change of physician and reset on change of physician?

Doing that doesn't produce the results I'd like...it's showing 1 case per abstract which shouldn't be the case.

Again, I only want to show where the same doctor has multiple occurrences of the same PIA date/time. If there is only one occurrence for that doctor, it shouldn't be counting the case. Make sense?

 

[lbass]

No, please reread my posts. PHysician should be group #1 for one thing.

-LB

 

[shelby55]

Hi LB

Sorry but it's not working.

I have two groups: #1 = provider and # 2 = provider assessment date time.

I created a running total which uses:
count(provider assessment date/time) as summary
on change of provider assessment date/time
reset on change of provider

I place the running total in the provider group footer but it only shows as one for each yet the detail data shows otherwise.

 

[lbass]

As I said earlier, that set up would return a 1 for each datetime that had two or more records and if instead you want to see a count of how many records there are belonging to a set of duplicated records you could change the evaluations to read for each record.

As a new approach, if you only want to count "extra" records, e.g., if one datetime has three records and you want the result to show 2 extra records, then you would have to use an evaluation formula like this in the evaluation section:

{table.datetime} = previous({table.datetime})

So it depends upon which of those three kinds of results you are looking for.

-LB

 

[shelby55]

Hi LB

Sorry if I'm being dense but I have no idea what you're saying.

What I would like is to show the number of cases where a physician has duplicate physician initital assessment times which is evidenced by having the same PIA for a doctor on the same day.

 

[lbass]

I'm talking about three different ways of setting up a running total. Please explain what you would expect to see in this case:

Dr A

9:15
9:15

10:23
10:23
10:23

2:15

4:20
4:20
4:20

What I was saying is you could either count how many datetimes contained duplicates (3), or how many datetimes were included that were part of sets of duplicated records (8), or you could count how many records were duplicates or "extras", and here the answer would be 5. I showed three different ways to set up a running total depending upon what you are after. Which is the result you are looking for? It seems to me you are saying either 3 or 5--which is it?

-LB