Identify duplicate records with key data elements missing 2

[jkupov]

Hopefully I don't complicate this more than it needs to be. I'm working with a database which, for identity protection purposes, is customer agnostic. I don't have any customer information to uniquely identify each record.

What I have been asked to do is to identify service repair records in which a technician was sent to the same location to work on the same device in the same day. So somehow, I need to tell CR to identify records with the same address (repair.adrs) in which the service was performed on the same date (repair.dt), and the service was performed on a device with the same manufacturer AND model number (sys.manf & sys.mod). I need these to be identified for each day. My customer is estimating that they have roughly 1,500 service repair calls per day nationwide, and of these, perhaps 10% might fot their criteria- but that is their best guess and a stab in the dark.

While they admit that a customer can potentially have two devices with the same manufacturer and same model # which need repair on the same day, they are convinced that the frequency would be so low to render it statistically insignificant. Their goal is to identify the frequency in which the initial attempt to repair was unsuccessful and a second technician had to be sent tot he scene. Unfortunately, their database is lacking some key elements which would help. I've recommended (among other things)that they create a numeric identifier for each customer rather than just a name and address and also recommended that they consider storing serial numbers in their database for each device.

Any help would be appreciated. Thank you!

JK

 

[fisheromacse]

i think i would start by inserting groups,
#1 on address
#2 on date
#3 on manufacturer
#4 on model

Place you fields in the detail sections, suppress it, then do a running total (distinct count of repair.dts?), and if the total is equal to or greater than 2 for group #4 your criteria is met.

then you could use the section expert to suppress any group #4's where the count was less than 2.

and i agree about adding fields to the database to make this more manageable!

 

[lbass]

I think you could do this by grouping in this order: address, date (on change of day), and a formula {@mfg-model} that concatenates mfg and model number. Then go to report->selection formula->GROUP and enter:

count({table.address},{@mfg-model}) > 1

You could use any recurring (non-null) field in place of {table.address}.

Then if you want to do any calculations across groups, use running totals, since the more usual summaries would include non-group selected records which are still in the report, but no longer visible.

-LB

 

[jkupov]

Thank you both- I should have gone to grouping right away. Seems like it has solved a lot of my problems in the past.

Now, this is where it is really getting hard for me. Not only do they want these identified, but they want to retain the last service call for those customers for the same day, while discarding the initial service call in which they failed to provide an acceptable resolution.

So the dataset that I will be using to analyse their business metrics will include all calls on which there was just one call placed to adequately resolve the customer's problem + the last service call record from those we identified above which ended up requiring a second service call to resolve the issue. They don't want to count the initial call because it didn't have a resolution, but they want to capture the last one where there was a resolution so I can run the analysis of their most common resolutions.

Does this make sense? So essentially, at this point I will need to discard the oldest record of those that have matches, and retain the more recent record in which a true resolution occurred... and add those back into the dataset containing all of the service calls with a "first call resolution".

One of these days I will get around to hiring an expert to do this database query stuff for me so I can focus on just analysing the business process! Until then- I am VERY GRATEFUL for your expertise!

JK

 

[lbass]

How do you determine that one visit resulted in a resolution? Is there some field that reports on status?

Also, are you working with a datetime field that would allow the time to determine the most recent record?

What about cases where there is a second visit on a different date?

-LB