Maximum Date between Columns

[AnthonyMJ]

Data for a Group 1

 ES_DATE   ES_DATE_H  CS_DATE  CS_DATE_H
+---------+---------+---------+---------+
 07/30/09  04/21/09  01/30/10  11/30/08 
 06/30/09  06/30/09  12/30/09  12/30/09
 07/30/09  07/30/09  08/15/09  10/20/09
 07/30/09  08/30/09  10/20/09  11/01/09

I would like to get the maximum date between ES_DATE_H and CS_DATE_H
where the ES_DATE_H <> any dates in ES_DATE and CS_DATE_H <> any dates in CS_DATE

Take the above example, here 04/21/09 and 08/30/09 are the only ES_DATE_H that is not present in ES_DATE.

while for CS_DATE_H, it is 11/01/09 which is the only date not in CS_DATE

Using the filter criteria, this is how the resulting table will look like in theory

Resulting Table

 ES_DATE_H  CS_DATE_H
+---------+----------+
 04/21/09   11/01/09
 08/30/09

Now, I need to get the maximum date between the two columns. The answer should be 11/01/09.

By the way, data above appears per group.


XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[AnthonyMJ]

The resulting table should actually look like this in theory

Resulting Table

 ES_DATE_H  CS_DATE_H
+---------+----------+
 04/21/09   11/30/08
 08/30/09   11/01/09

The maximum date between the 2 columns is still 11/01/09.

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[Madawc]

If you group records, you can get summary totals which may specify the minimum or maximum. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.

Your aim could be achieved by putting the minimum and maximum for the two fields in the group footer - or group header if you'd prefer. Suppress the 'maximum' if it is the same as the maximum. (Right-click on the field for formatting options, including suppression.)

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

 

[AnthonyMJ]

The challenge here is arriving at the resulting table. We need to check first for non-existency of dates for ES_DATE_H and CS_DATE_H in ES_DATE and CS_DATE respectively. Once we have completed this task, we can then apply the automated totalling in CR.

Ideas ?

XIR2 on Unix Solaris
Informatica 7.1.3
CRXIR2, Oracle 9i/10g

 

[Madawc]

Running totals can make tests like that.

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

 

[lbass]

Assuming these fields are from one table (A), you could add the table twice more, using left outer joins to link FROM A.ES_DATE_H TO A_1.ES_DATE, and to link FROM A.CS_DATE_H TO A_2.CS_DATE.

Then use a record selection formula like this:

isnull({A_1.ES_DATE}) or
isnull({A_2.CS_DATE})

Then use a formula like this to return each set of dates:

//{@ES_DATE_H}:
if isnull({A_1.ES_DATE}) then {A.ES_DATE_H}

//{@CS_DATE_H}:
if isnull({A_2.CS_DATE}) then {A.CS_DATE_H}

Then create a formula like this for the Group #1 header or footer:

maximum([maximum({@ES_Date_H},{table.group1field}),maximum({@CS_Date_H},{table.group1field})])

-LB