Average byr date difference

Applications Grid / Reports
#1

Hi
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?

Thanks
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…

Thanks
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 = ‘’;
sc_set_global($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 :frowning:
Any Idea?

Mario Wojcik

Sin t&#237;tulo.png