Date Selection Problem

[Bharrell]

I have a production report I am working on. One of the tables I am using date and time stamps when the status is changed from one to the next. My problem lies where the status changes back and forth between one another. I need to be able to pull the earliest date fo a particular status.

Example:
Status 1 changed_date
Status 2 changed_date
Status 3 changed-date
Status 2 changed_date

I need to be able calculate the difference between Status 1 and Status 2 using the earlier Status 2 date.

Any help would be appreciated.

BHarrell

 

[synapsevampire]

Are you saying that you have multiple dates for each Status and you want to compare the earliest date of each status?

You mention that dates change back and forth, so I may be misunderstanding you.

If you want the earliest Status 1 date, and then when you get to the earliest status 2 date, show the difference, this might be a suitable cheat:

group by Status and then Change_Date

In the Status group header, place a formula akin to the following:

name: @GetLowestDate
whileprintingrecords;
global datevar LowestDate := {MyTable.Changed_Date};

In the same group footer place a formula like:
name: @ShowDateDiff
whileprintingrecords;
global datevar LowestDate;
next({MyTable.Changed_Date})-LowestDate

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Bharrell]

I apologize for be to vague. My report is to track loans as they progress thru the different loan status'. When a loan status is changed there the date of that staus change is stored in the DB. This may help:

Status_Code Change_loan_date
1 2002-07-05
2 2002-07-05
3 2002-07-16
2 2002-07-16
3 2002-07-18
4 2002-07-18
5 2002-07-26

I think this should as faras the status changes. What you have given me has got me pointed in the right direction and I truly do appreciate it.

Bharrell

 

[SmpTraining]

With some modification SV's solution is still the way to go.

1. At the GH level, initialise a variable with the date value.
2. At the Details level, write a formula to check whether the current date variable is lower than the stored var, if it is, assign the new date to the variable.
3. At the GF level, the variable will hold the lowest value.

As you have no doubbt noticed, I've not mentioned the fact that you need to do this for all status codes. You can either create 5 variables (if you have five status codes), or you can use arrays if you need to be more flexible.

Also remember that at the GH level, you should initialise all the variables before using them. Otherwise they may contain values from the previous loan. Steve Phillips, Crystal Consultant