CODEWITHSUNDEEP
phpmysql
Prudential Developer
Prudential Developer

Reputation: 157

How to take sum of time value using sql query?

I have a ReportBuilder class for generate document (Excel). what i want is selecting employee and date period then calculate total working hours for that date period. this is my php file 

<?php
class EmployeeSummaryTimesheet extends ReportBuilder{

    public function getMainQuery(){
        $query = "SELECT
(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',
(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',

TIMEDIFF(out_time, in_time) as 'Working Hours'
FROM Attendance at";

        return $query;
}

public function getWhereQuery($request){
        if(($request['employee'] != "NULL" && !empty($request['employee']))){
            $query = "where employee = ? and in_time >= ? and out_time <= ? group by employee;";
            $params = array(
                $request['employee'],
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }else{
            $query = "where in_time >= ? and out_time <= ? group by employee;";
            $params = array(
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }
 return array($query, $params);
    }}

now i cant get sum of that particular date period im getting first day TIMEDIFF(out_time, in_time) value only not sum of working hours help me

EDit - Here i have two table Employee and Attendance (id,employee(this is employee id from Employee table),in_time,out_time)

Edit : i take off DATE_FORMAT(in_time, '%Y-%m-%d') as 'Date', i dont need this column

Upvotes: 4

Views: 234

Answers (2)

Prudential Developer
Prudential Developer

Reputation: 157

This is the answer i did my self 

    public function getMainQuery(){

        $query = "SELECT
(SELECT `employee_id` from Employees where id = at.employee) as 'Employee',
(SELECT concat(`first_name`,' ',`middle_name`,' ', `last_name`) from Employees where id = at.employee) as 'Employee',
DATE_FORMAT(in_time, '%Y-%m') as 'MONTH',
CONCAT(
FLOOR(
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))<=0 THEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))=''
ELSE TIME_TO_SEC(TIMEDIFF(out_time,in_time)) END)/3600) ,':',
FLOOR(MOD(
SUM(CASE WHEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))<=0 THEN TIME_TO_SEC(TIMEDIFF(out_time,in_time))=''
ELSE TIME_TO_SEC(TIMEDIFF(out_time,in_time)) END),3600)/60)) as 'WORKING HOURS'
FROM Attendance at";

        return $query;

    }

    public function getWhereQuery($request){
        if(($request['employee'] != "NULL" && !empty($request['employee']))){
            $query = "where employee = ? and in_time >= ? and out_time <= ? order by in_time;";
            $params = array(
                $request['employee'],
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }else{
            $query = "where in_time >= ? and out_time <= ? group by employee;";
            $params = array(
                $request['date_start']." 00:00:00",
                $request['date_end']." 23:59:59",
            );
        }

        return array($query, $params);
    }
}

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

You want one record per employee and date and display the employee's total working hours for that day. So join and group by employee and date and sum the working hours.

select
  e.employee_id,
  concat(e.first_name, ' ', e.middle_name, ' ', e.last_name) as employee_name 
  date_format(at.in_time, '%Y-%m-%d') as 'Date',
  sum(timediff(at.out_time, at.in_time)) as 'Working Hours'
from attendance at
join employee e on e.id = at.employee
where ...
group by e.employee_id, date_format(at.in_time, '%Y-%m-%d');

Upvotes: 1

Related Questions