| To add up or accumulate time can be tricky in any data base program. In
this example, the table is named TEMP_TIME. The field that we're going to add up is called
DURATION. It's formatted as Date Time in either SQL server or Access. Each record
contains values like "06/15/2001 00:15 AM. The 00:15 is the part we're going to get,
as in a 15-minute call.
Make three formulas in Crystal: (upper or lower case doesn't matter)
@HOURSField=Hour({TEMP_TIME.DURATION})
@MINUTESField=Minute({TEMP_TIME.DURATION})
@SECONDSField=Second({TEMP_TIME.DURATION})
Hide these fields in your report. Insert the DURATION field in the detail section and
don't hide it. We want that to display, and underneath it we'll add up the values.
Make a grouping on the DURATION field
Sum the values of each of these formulas in the DURATION group section, but put them
out of the way (like, far to the right) and hide them.
Make three more fields in the grouping section. Two of these are also going to be
hidden
@SubHours=
Sum ({@hoursfield}, {TEMP_TIME.DURATION})+
truncate(Sum ({@minutesfield}, {TEMP_TIME.DURATION})/60)+
remainder({@SubMinSec},60)/100
@subMinSec=
Sum ({@MinutesField}, {TEMP_TIME.DURATION})+
truncate(Sum ({@SecondsField}, {TEMP_TIME.DURATION})/60)+
remainder(Sum ({@SecondsField}, {TEMP_TIME.DURATION}),60)/100
@SumTime=
right(totext({@subhours}/100),2)+":"+
right(totext({@SubHours}),2)+":"
+right(totext({@SubMinSec}),2)
The @SumTime field is the only one you need to display, and should be just below the
DURATION field of the detail section. The results will look like this:
03:06:10, which is 3 hours, 6 minutes and 10 seconds
Mini Solve Home
|
