How can I retrieve specific changes in an journal table?

[qu1ncy]

Hello,

I have been asked to capture all employee name changes for the last 5 years and I’m a little lost with the PL_SQL required, or if this info can be retrieved using a Crystal formula.
We have an Employee_Info_Journal table in our HR db which creates a before & after record every time data is changed in the master Employee_Info table.
The records in the journal table look like the following:

Operation Which Fname Lname DateModified

U B Lilly Smith 2004-10-20
U A Lilly Brown 2005-09-26


‘Operation’ represents “U” for Update, “D” for Deletion, etc…
‘Which’: “B” refers to the before record, “A” the after record.
The info in all other fields in this journal table is the same in both B & A records.

Could you please advise?
Many thanks.

 

[Madawc]

You don't say which bits of the task you need advice on. So I'll give you an outline.

To get details for the last five years, Datediff("yyyy", {your.date}, currentdate) <= 5

Group by employee number, and by surname + forename within employee number. Create a single formula field, maybe called @FullName for surname + forename. THis should allow for nulls if you have them on your database.

Do 'minimum' and 'maximum' for @FullName: you get this by right-clicking, choosing Inert and then Summary.

Use Report > Selection Formulas > Group. Suppress all employee number groups where the 'minimum' and 'maximum' for @FullName are the same: i.e. the name has not changed.

You'll probably have far too many detail lines in each group. If so, display details in the group header or footer of the name group. Suppress the rest.

All of this is based on Crystal 10. For other versions of Crystal it would be slightly different but should still work. It's best to specify your Crystal version.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[qu1ncy]

Hello Madawc,

Thanks for your response. We're using CR 8.5 with XP. I should have been more informative... sorry!
The task I'm trying to achieve is provide my client dept with a report that will pull all name changes in the given period of time. I'll give this a try.

Appreciated!
Q