No announcement yet.

Calculating Time Total in Grid

  • Filter
  • Time
  • Show
Clear All
new posts

  • Calculating Time Total in Grid

    I wonder if anybody could give me any ideas as to how to calculate the total time on a grid 'Group By', and would also be nice to utilise the same method on a PDF Report later on.

    Put simply, I have a series of records, each have a start and end DATETIME field, and I am calculating the difference between the times using TIMEDIFF and then inserting that in to the DB against each record every time the grid loads to ensure it is up to date, however... what I cannot work out how to do, is to calculate the total time of all records on the 'Group By' selections as this only gives the option of COUNT for time fields.

    Is there a simple way of this, or does the total time need to be converted to seconds to calculate as a SUM and then converted back to time format, and if so, what is the best method within scriptcase?

    Thanks in advance

  • #2
    I agree, this would be very helpful and I haven't yet found a solution to it.


    • #3
      Just make a new custom field, set it as integer (ACCUMULATED checkbox shoul appear now), set ACCUMULATED and select the field to accumulate.

      That simple


      • #4

        I tested the solution, but somehow it doesn't work. What am I doing wrong?

        In my grid I do a grouping by device_id and checkall_res. I created a new custrom-field result and set it to integer and ACCUMULATED.
        But I don't get my TimeDiff field when I select it, only if I also set field TimeDiff also to integer or decimal.
        Then under group by, I can add my field to sum and select sum.

        But the result shown in grid is always 0 (zero) or nothing.

        Here is the Sql:

        TimeDiff(stat.tsbis, stat.ts),
        Order By
        stat.stat_id Desc

        And these are a few values that the SQL returns.
        # device_id, checkall_res, ts, tsbis, TimeDiff(stat.tsbis, stat.ts), stat_id
        '5C244D1FE924', '20', '2019-09-11 15:43:31', '2019-09-11 15:43:31', '00:00:00', '412897'
        '5C244D1FE924', '10', '2019-09-11 15:43:29', '2019-09-11 15:43:31', '00:00:02', '412896'
        'A480389DBEEB', '10', '2019-09-11 15:41:16', '2019-09-11 15:41:16', '00:00:00', '412895'
        '5C244D1FE924', '20', '2019-09-11 15:41:15', '2019-09-11 15:43:29', '00:02:14', '412894'
        '5C244D1FE924', '20', '2019-09-11 15:41:13', '2019-09-11 15:41:15', '00:00:02', '412893'
        'A480389DBEEB', '10', '2019-09-11 15:40:46', '2019-09-11 15:41:16', '00:00:30', '412892'

        Thanks for helping!