Comparision of Date fields

[RptSm]

I need a derived field or formula that looks at status =A and Rehire date > Term Date = Rehire else Hire date. I am using PCPW 5.2 Oracle database. Any help is greatly appreciated.

 

[CharlesCook]

What do You want to return if Status is not A?

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[RptSm]

A null value, I guess. My issue is that I have active employees with a term date & a rehire date but the rehire date should be greater than the term date. I hope this made sense?

 

[CharlesCook]

This should work:

DECODE(Status,'A',
CASE WHEN rehiredate > termdate then rehiredate
ELSE termdate
END,
NULL)

THE UNDERLINED AND BOLDED BITS NEED TO BE THE ACTUAL DATA FIELD NAMES.

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining

 

[RptSm]

Thanks this works. Except I am still getting 2 rows. Should Term date & Rehire date be in query only,so that I just get the most recent (active) record?

Using this case function is new to me can it be used to handle multiple criteria like a nested if statement? Any examples would be great.

 

[CharlesCook]

I think this will help with the multi row issue

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-DatabaseGrouping.pps

Here is some info on the case statment:

http://www.databasejournal.com/features/oracle/article.php/3344871

CharlesCook.com
ADP - PeopleSoft - SAP
ReportSmith - Crystal Reports - SQR - Query - Access
Reporting - Interfaces - Data Mining