No announcement yet.

Average byr date difference

  • Filter
  • Time
  • Show
Clear All
new posts

  • Average byr date difference

    I need help about a average on total line.
    I have this table
    Constr. Bulldozer Date Fuel
    1 21 2015-06-01 200
    1 20 2015-06-20 210
    1 22 2015-07-01 198
    2 12 2015-05-06 212
    2 11 2015-05-07 150

    If I use SC average, construction1 = 608 / 3 and construction2 = 362 / 2
    I need a average by date, then construction1 = 608 / (2015-07-01 - 2015-06-01) = 608 / 30
    and construction2 = 362 / (2015-05-07 - 2015-05-06) = 362 / 1

    Any idea about how I can get this average?

    Mario Wojcik

  • #2
    Ir order to do that I would choose to use something like
    select max(Date) as mx,min(Date) as mi,sum(fuel) as totalfuel from mytable group by constr
    That should give you the begin and end dates and total fuel use in a list. Then it is just a matter of totalfuel/(mx-mi) with mx and mi in days.


    • #3
      Hi, thanks by answer
      The problem is that I use advanced search to filter by date range.
      I can do this sql statment on "ongroup by all" event but I need the filters values ({sc_where_filter} is undefined until I entry a first range on advanced search)...
      I send this query:
      SELECT constr, (sum(Fuel) / (datediff(day, min(Date), max(Date))+1)) as PromCon
      FROM LubriCons
      WHERE Date >= '2014-06-01' and Date <= '2014-06-06' and constr=2
      GROUP BY constr
      and work fine, but I don't know where SC save actual break constr and date filter...

      Mario Wojcik


      • #4
        I think I am not fully getting it..

        If you want a filter that is undefined you can use the following tricks

        select .your stuff here.... from LubriCons where ... and {sc_where_filter} GROUP by constr
        Per default set your sc_where_filter to 1=1 when you do not filter and change it to something more interesting when you want to filter.
        Thanks to variables you can do a lot of this stuff.
        So per default you would get:
        select .your stuff here.... from LubriCons where ... and 1=1 GROUP by constr


        • #5
          Well peoples, I find a solution:

          1.- Setting the grid to summary and search init.
          2.- Add a new hide field called PromedioC
          3.- Group the grid by Constr (static)
          4. -On applicationinit event I create this global var:

          $GFiltro = '';

          5.-In OnRecord event get the filter value, if exist
          if (isset({sc_where_filter})) {
          [GFiltro] = {sc_where_filter};
          6.-Into OnGroupByAll event send a sql command and change the {sum_quebra_...... value
          $check_sql = 'SELECT Constr, (datediff(day, min(Date), max(Date))+1) as PromCon '
          . ' FROM LubriCons'
          . ' WHERE Constr = ' . {CONSTR} . ' AND ' . [GFiltro]
          . ' GROUP BY CONSTR';
          sc_lookup(rs, $check_sql);
          if (isset({rs[0][0]})) { // Row found
          $Days = {rs[0][1]};
          else { // No row found
          $Days = 1;
          {sum_quebra_promedioc} = ({sum_quebra_gasoilagregado}) / $Days;

          Whit this, each line of average were calculated very well but I canīt fix total average
          Any Idea?
          Sin t&amp;#237;tulo.png
          Mario Wojcik