Trouble evaluating time 2

[Charley71]

I have a report that looks like this:

Wilson, Ted

10/19/07 9:26 am
10/19/07 10:56 am
10/21/07 8:00 am
10/21/07 8:50 am

and so on.

What I need to do is evaluate the time difference for every 2 records. I would also like to be able to have a label that will say "On Time" for the lower of the two records and the higher is "Off Time" in addition to displaying the date/time. I use crystal reports xi. I am grouping by name. My datetime field is called "time". I am not sure how you get it to look at only 2 records at a time in a details section. Any help would be appreciated. Thanks

 

[Madawc]

time difference for every 2 records

Which two records? If it's within the day, group by day and then compair minimum to maximum.



Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[Charley71]

Actually, there could be several records for any given day. So I could have 4 on/off times for one day or more.
I would like it to look like this:

Johnson, Lee
10/1/07 10:00AM ON
10/1/07 10:30AM OFF 0:30
10/1/07 3:00PM ON
10/1/07 3:300PM OFF 0:30
10/2/07 2:00PM ON
10/2/07 3:00PM OFF 1:00

assuming the lower time is the ON time and the later is the OFF time. I don't have to put in ON/OFF but it would be nice. That information is not in the database, it is only an assumption that the lower time of the two is ON and the later is the OFF.

 

[Charley71]

This works for every other record

whileprintingrecords;
numbervar diff := 0;
diff := {testtime_.Time}-previous({testtime_.Time});

Is there something I can add that tells every two records instead?

 

[Madawc]

Use PREVIOUS to check the time for the previous record. Do this only for the OFF records, if you have that detail.

If it needs to be by count, do a running total count within the group. Test

Remainder({#MyCount}, 2) = 0

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

whileprintingrecords;
numbervar diff := 0;
stringvar onoff;
if remainder(recordnumber,2) = 0 then
onoff := "Off" else
onoff := "On";
if onoff = "Off" then
diff := {testtime_.Time}-previous({testtime_.Time}) else
diff := 0;

You can format the formula to suppress if zero.

You can then show the result of onoff in a separate formula:

whileprintingrecords;
stringvar onoff;

-LB