Help with SQL to sum daily between dates

Asked

Viewed 334 times

1

I have two tables: company and walking

In the table walk, I have the input date, date, value I would like to select all companies, and list it and the total amount the company spent on daily rates, only if the date is different from '0000-00-00 00:00:00' in the case. If you haven’t completed the daily rate, like 1 day and 20 hours for example, the daily rate is counted equal. Always rounded up.

I am using PHP. I’m stuck in this SQL that needs to first find out how many daily gave in total for each truck, then make times the daily value, and then add up all the totals and generate the total that was spent by each company.

$sql = mysql_query("SELECT * FROM empresa WHERE emp_ativo = '1' ORDER BY emp_nome ASC");
while ($linha = mysql_fetch_assoc($sql)){

    $sql_valor = mysql_query("SELECT * FROM caminhao WHERE cam_ativo = '1' AND emp_id = '".$linha['emp_id']."' AND cam_datasaida != '0000-00-00 00:00:00'");
    while ($val = mysql_fetch_array($sql_valor)){
        ...
    }

    $lista[] = $linha;

}
php mysql
  • 1

    post tables templates, preferably on Sqlfiddle to build the query

  • I would make a Function , which would receive input date and date output and calculate the number of days (rounding) select would be simpler select id,sum(qtd_days(input date, date output)) qtdduas from caminhao group by id , not publish as solution as Function itself is missing.

  • You’d better pass the templates like Caputo said, but maybe what you want is something like: SELECT e.emp_id, e.emp_name, SUM(c.cam_valordiaria) AS total_diarias FROM company e JOIN caminhao c ON c.cam_active = '1' AND c.emp_id = e.emp_id AND e.cam_dataoutput != '0000-00-00 00:00:00' WHERE e.emp_active = '1' GROUP BY (e.emp_id) ORDER BY e.emp_asc name

1 answer

1

You can use the function DATEDIFF of mysql. The syntax is as follows:

DATEDIFF(date1,date2)

In a query would look like this:

SELECT DATEDIFF('2015-07-24', '2014-07-24') AS DiffDate

The return is:

+----------+
| DiffDate |
+----------+
|      365 |
+----------+

365 being the difference between 07/24/2015 and 07/24/2014.

If you need the difference in hours, you can use the function TIMEDIFF, whose syntax is the same:

TIMEDIFF(expr1,expr2)

In a query would look like this:

SELECT TIMEDIFF('2014-07-28 12:45:00', '2014-07-24 10:55:00') AS DiffTime

The return is:

+----------+
| DiffTime |
+----------+
| 97:50:00 |
+----------+

If you need the difference in decimal, 1.5 hours for example, we can use TIMEDIFF together with the function TIME_TO_SEC, whose syntax is as follows:

TIME_TO_SEC(time)

In a query would look like this:

SELECT TIME_TO_SEC(TIMEDIFF('2014-07-24 16:48:00', '2014-07-24 07:00:00'))/3600 AS DecimalTime

The return is:

+-------------+
| DecimalTime |
+-------------+
|      9.8000 |
+-------------+

I hope I’ve helped :)

Updating

As you need the value in days, just divide the obituated result by 24, getting as follows:

SELECT TIME_TO_SEC(TIMEDIFF('2014-07-24 16:48:00', '2014-07-24 07:00:00'))/3600/24 AS DecimalTime

The return is:

+-----------------+
| DecimalTime     |
+-----------------+
|      0.40833333 |
+-----------------+

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.