Comparing Data in an Group

[twilliamson2]

I am using CR X with an Oracle database.

I have to compare a set of history data for an employee and create a RECORD NUMBER (RCD#).

The data is grouped by SSN (Group#1) and it is sorted by Effective Date (EFFDT).
Can someone help me put the logic explained below into an actual formula? I am stuck, please help.

The logic for RCD# is as follows:

If the Company is the same for the group of record in Group#1
And
If the Title is the same for the group of record in Group#1
And
If the Department is the same for the group of record in Group#1
And
If the Start_Date and the End_Date is the same for the group of record in Group#1
Then
The RCD# should be the same starting with zero
Else
Increment RCD# by one

SAMPLE RECORDS for what RCD# is suppose to be.

Group #1: SSN (111-11-1111)

RCD# EFFDT TITLE_CD DEPT_CD COMPANY APPT_BEG_DT APPT_END_DT
0 05/01/2004 4000 1071 NNA 09/01/2006 08/31/2007
0 05/01/2005 4000 1071 NNA 09/01/2006 08/31/2007
1 08/29/2005 9000 7900 AFG 08/29/2005 01/25/2006
1 08/29/2005 9000 7900 AFG 08/29/2005 01/25/2006
2 08/29/2005 9000 7450 CJJ 08/29/2005 12/31/2005
3 08/29/2005 9000 8440 CJJ 08/29/2005 12/31/2005
1 01/25/2006 9000 7900 AFG 08/29/2005 01/25/2006
0 05/01/2006 4000 1071 NNA 09/01/2006 08/31/2007
4 08/27/2007 9000 7100 KLL 08/27/2007 01/24/2008
4 08/27/2007 9000 7100 KLL 08/27/2007 01/24/2008
5 08/30/2007 1000 2855 NNA 08/30/2007 05/24/2008
5 08/30/2007 1000 2855 NNA 08/30/2007 05/24/2008
0 09/01/2007 4000 1071 NNA 09/01/2007 08/31/2008

 

[lbass]

I don't think you have documented all the logic as I can't track your sample results back to what you have specified.

-LB

 

[Madawc]

I think you need a running total, or maybe a variable. You can find minimum and maximum values for a group using summary totals, and then count once per group if the rules apply.

If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

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