same field calculations

[wolverinec7]

the following is a piece of data from our tracking system database (one table)

ID status assembled
YL04UTB(group) 2006-01-18 07:28:27 (min formula)

18 2006-01-18 07:28:27
143 2006-01-18 10:16:10
18 2006-01-18 11:25:59
143 2006-01-18 11:35:53

Group footer 2006-01-18 11:35:53 (max formula)
also at GF 3hrs 53+mins (min-max diff)
status 18 is when he starts his engine, status 143 when he stops. i need to work out how much of his working day is spent driving......basically the diff between a 143 status and previous 18 status and be able to add these up.

 

[lbass]

Create a formula for the detail section:

whileprintingrecords;
numbervar secs;
numbervar sumsecs;

if {table.status} = 143 then
secs := datediff("s",previous({table.assembled}),{table.assembled});
sumsecs := sumsecs + secs;

Place a reset formula in the group header:

whileprintingrecords;
numbervar sumsecs := 0;

Place a display formula in the group footer that makes use of SynapseVampires FAQ on converting seconds to hhmmss:

whileprintingrecords;
numbervar sumsecs;
numberVar hrs;
numberVar min;
numberVar sec;
stringVar hhmmss;

hrs := Truncate(Truncate(sumsecs/60)/60);
min := Remainder(Truncate(sumsecs/60),60);
sec := Remainder(sumsecs,60);
hhmmss := totext(hrs, "0") + ":" + totext(min, "00") + ":" + totext(sec, "00");
hhmmss

-LB

 

[wolverinec7]

this almost works however it creates a running total almost. because we have no way to identify each past journey as being linked (i.e. each 18 has a 143) then i can only group it by vehicle id so all his journeys appear together in the details section

i.e this is how it appears for some of yesterday
instead of
0 0
10063 10063
20126 0
20720 594
21314 0
21462 208

 

[lbass]

You said you wanted them added up in your first post, which is what the formula does. Please clarify what you want your results to be.

-LB

 

[wolverinec7]

sorry i'm not the greatest at explaining i want to work out the diff between each 18 (engine start) and 143(engine off) event and then be able to sum these to work out how much of the engineers day is spent actually driving. the problem i have is there is no unique identifier to link the 18 & 143 events together, all i can do is sort them in order and group by day

 

[lbass]

If you change the accumulation formula to:

whileprintingrecords;
numbervar secs;
numbervar sumsecs;

if {table.status} = 143 then
secs := datediff("s",previous({table.assembled}),{table.assembled});
sumsecs := sumsecs + secs;
secs;

The difference per set of records will display. I'm not sure of what all of your groups are, but for whatever group level you want the sum, you need the reset formula in that group header, and the display formula in that group footer. If this still doesn't work as you like, please identify each group and state where you want the sum(s).

-LB