How to calculate the spent time

[andrebrazil]

Dears I have a report showing

Purchase Order / initial state / Final State / Time stamp
12345 / null / create / 12-mar-12
12345 / create / status 1 / 15-mar-12
12345 / status-1 / status 2 / 20-mar-12
12345 / status-2 / complete / 22-mar-12
...
with several orders

Is it possible to create a formula with the result

Result 1
Purchase Order / initial state / Final State / Spent time
12345 / null / complete / 10 days

Result 2
Purchase Order / initial state / Final State / Spent time
12345 / status-1 / complete / 2 days

Thank you

 

[MCuthill]

andrebrazil,

I think you could use variables, with resets on the Purchase Order Group-level to achieve the sought results. I do not have Crystal Reports in front of me today, but I think something like the following should work.

Group: Purchase Order
- Detail: Fields as per your original post, sorted ascending by date.

{@VariableResets} -- Place in Group Header

WhilePrintingRecords;
Shared DateVar NullStartDate:="";
Shared DateVar Status1StartDate:="";
Shared DateVar CompletedEndDate:="";

(I am not sure of syntax for date resets, you may need to "reset" with a date such a Jan 1, 1900 or something)

{@Accumulator}

WhilePrintingRecords;
Shared DateVar NullStartDate;
Shared DateVar Status1StartDate;
Shared DateVar CompletedEndDate;

IF {table.initialstate}="Null" THEN NullStartDate:={Table.TimeStamp};

IF {table.initialstate}="Status1" THEN Status1StartDate:={Table.TimeStamp};

If {table.finalstate}="Completed" THEN CompletedEndDate:={Table.TimeStamp};

(the first IF may need to be "IF IsNull({table.initialstate}) THEN...".)

{@Display_Result1}

WhilePrintingRecords;
Shared DateVar NullStartDate:="";
Shared DateVar CompletedEndDate:="";
Shared NumberVar Result1;

Result1 := DateDiff("d",NullStartDate,CompletedEndDate)

Result1;

{@Display_Result2}

WhilePrintingRecords;
Shared DateVar Status1StartDate:="";
Shared DateVar CompletedEndDate:="";
Shared NumberVar Result2;

Result2 := DateDiff("d",Status1StartDate,CompletedEndDate)

Result2;

There may be some syntax cleanup required on the above, but I think it *should* be close.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[andrebrazil]

Mike I was out Crystal Reports for a while and just today I tested your suggestion.

Thank you very much, it was necessary to make some changes but it worked perfectly.

**************************
Variable resets
WhilePrintingRecords;

Shared DateTimeVar NullStartDate:= DateTime(0,0,0);
Shared DateTimeVar Status1StartDate:= DateTime(0,0,0);
Shared DateTimeVar CompletedEndDate:= DateTime(0,0,0);

**************************
Here I changed to "h" instead of "d" because some orders can changed on the same day.

Result1 := DateDiff("d",NullStartDate,CompletedEndDate)