How to get a specific record?

[kate8]

Hi,

I need create a crystal report to show the most recent date when people’s status becomes negative.
In a time period, for example, from March 1 to today, a person’s status became -1 in March 10, and then became positive on May 2, but on May 20, it became -1 again. Now it is -1. I need display the May 20 data in my report. I tried to select all the people with -1 status, and group them by person’s ID, then insert a minimum date. But what I got is March 10. But since after May 2, the status became positive, so March 10 is not what I need. How can my report have May 20 selected, which is the most recent date the status became -1?
If I do it in Java, I can do a loop starting from most current date to pick the 05-20-09 record out. But how can I do it in Crystal Report?

Example:

ID Status Entry_Date

111 0 03-09-09
111 -1 03-10-09 --- not this one
111 -1 03-12-09
111 -1 04-02-09
111 0 05-02-09
111 0 05-08-09
111 -1 05-20-09 --- I need this record in my report
111 -1 06-01-09
111 -1 06-09-09

I hope I explain my question clear.

Thanks a lot for any helps, suggestions!!!!!

 

[dunlop1975]

Do what you did but choose a maximum date instead (you want the most recent date)

 

[kate8]

Thanks,dunlop1975!

I did not explain clear enough.
I need the most recent starting negative date, not the most recent date. So in my example, it should be 05-20-09, not 06-09-09.

I don't know if that is possible in Crystal Report.

Thank you!!

 

[dunlop1975]

No you were clear, I read the problem to fast.

Assuming that your only values are 0 and -1, make sure the date is grouped in ascending order and place the following formulas in the report:


<Init Formula - Place in GH>
shared datevar dtEntryDate := date(0000,00,00);

<Min Date Formula - Place in Details>
shared datevar dtEntryDate;
if {Status} is = -1 and previous({Status}) = 0 then dtEntryDate := {Entry_Date}

then call the variable dtrEntryDate in the GF.

I'm sure this could be accomplished easier with a Running total as well I just can't access my PC at the moment to test this.

 

[dunlop1975]

Date is sorted in ascending order not grouped

Sorry I'm on a loaner PC that is slower than mud atm, I'm going to have to stop posting until tomorrow ; )

 

[kate8]

Thank you, dunlop1975!!!
I am trying.

 

[kate8]

It is working!
Thanks a lot,dunlop1975!!!

One more question, is it possible sorting the group by dtEntryDate?
Since all the data show in the GF,there is no way for me to sort the report by the dtEntryDate. It doesn't even in the field list for summarize. Is it because that is a shared variable?

Thanks,again!!