Selecting status from dates

[Khawer]

Hello,

I am on CR V.9 SQL. I have a quick question on how to create a formula based on status of an employee. Lets say

Employee | Status | Date
Tom Active 11/04/94
Tom Leave 12/04/00
Tom Term 03/05/01

Bill Active 07/01/99

John Active 04/01/98
John Leave 04/02/04

Bob Active 03/05/00
Bob Term 04/04/01
Bob Active 07/09/03
Bob Leave 09/29/05


Is there any way to grab the status of an employee based on the max date. This has to be done in one formula in the detail section. If someone can please point me the right direction, that be great. Thanks in advance

 

[paulmarr]

Formula placed in Report Header and supressed:

global datevar maxdate := #01/01/1980#;

________________________________________

Formula placed on detail line:

global datevar maxdate;
global stringvar status;

if {database.date} > maxdate then

( maxdate := {database.date};
status := {database.status};
_____________________________________
Formula placed on Group footer (Group by employee):
global stringvar status;
local stringvar show := status;
status := "";
maxdate := #01/01/1980#;
show

______________________________________

Cheers
paulmarr

 

[bdreed35]

Try this:

//@ShowStatus
if maximum({table.date},{table.employee}) = {table.date}
then {table.status}
else ""

Place the formula in the details section.


~Brian

 

[lbass]

If you only want to display the most recent record in the detail section, then you could either use group selection by going to report->selection formula->GROUP and entering:

{table.date} = maximum({table.date},{table.employee})

...or you could use "Add command" as your datasource (database->database expert->your datasource->add command and entering something like:

Select table.`employee`,table.`status`,table.`date`
From `table` table
Where table.`date` = (select max(A.`date`) from table A where A.`employee` = table.`employee`)

-LB

 

[Khawer]

Thanks alot let me give it a try