Time Difference Calculation

[runningphan]

I have kind of a tricky time difference calculation (well, tricky for me ).

I need to calculate the difference between date/time fields in different records. Each record can have one of three types (started = 3, stopped = 4, and finished = 6) and then the status type date/time. Well, I need to calculate the difference between the start and stop records and if there isn't a stop record between the started and finished record the difference between the started and finished record.

Scenario 1

Record 1: {table.status} = 3 {table.status_date} = 5/18/2006 9:30:00 AM
Record 2: {table.status} = 4 {table.status_date} = 5/18/2006 9:35:00 AM
Record 3: {table.status} = 3 {table.status_date} = 5/18/2006 9:50:00 AM
Record 4: {table.status} = 4 {table.status_date} = 5/18/2006 9:57:00 AM
Record 5: {table.status} = 6 {table.status_date} = 5/18/2006 9:58:00 AM

This would produce a calculated time of 12 min.

Scenario 2

Record 1: {table.status} = 3 {table.status_date} = 5/18/2006 9:30:00 AM
Record 2: {table.status} = 6 {table.status_date} = 5/18/2006 9:58:00 AM

This would produce a calculated time of 28 min...

Any insight on this would be GREAT!!!!

Thanks a bunch.

 

[synapsevampire]

Group by whatever field this scenario is for.

Then create 3 formulas:

Group header formula:
whileprintingrecords;
timevar AllTime := ctime(0,0,0);
booleanvar TimeStatus:=False

Details formula;
whileprintingrecords;
booleanvar TimeStatus;
timevar AllTime;
if {Table.Status} in [4,6]
and previous({table.status}) = 3 then
AllTime:=AllTime+datediff("n",{table.status_date},previous({table.status_date}));

Group Footer formula (display the results here):
whileprintingrecords;
timevar AllTime

I made some logical assumptions that the start will always happen just before a stopped or finished, and that a finished is the last action.

If not, then this will need to be elaborated on, and the coders need to be replaced.

-k

 

[rosemaryl]

Dear runningphan,

You don't indicate what database or version of Crysatl. I often resort to a sql expression for these types of things which can be more efficient.

The code can be tedious to write, but you end up with seconds on each record that can be rolled up easily and since sql expressions are not whileprintingrecords the results will be availbale for charting and such.

regards,

ro


Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762