Clorae Clorae - 4 months ago 10
SQL Question

How to get the total from 2 column in a week

How can I get the Total Undertime for each employee per week. per month. I have a

view
in which it has columns
undertime
and
overtime
per day per employee. however i need to get the
TotalUndertime
per week.

|EmpID |DayofWeek|DatePresent|Overtime |Undertime|
|3050001|Friday |2016-04-01 | |00:01:00 |
|3050001|Monday |2016-04-04 | |01:00:00 |
|3050001|Tuesday |2016-04-05 |00:30:00 | |
|3050001|Wednesday|2016-04-06 |00:30:00 | |
|3050001|Thursday |2016-04-07 |00:05:00 | |
|3050001|Friday |2016-04-08 |00:05:00 | |


If the employee has an
Undertime
on
Monday
, the employee can pay for the
Undertime
on the following days from
Tuesday - Friday
. Or if the Employee has an
Undertime
on
Tuesday
, the employee has
Wednesday - Friday
to pay for the
Undertime. The
TotalUndertime = "00:01:00"` in the table shown above.

I'm just a newbie when it comes to mysql queries using date and time. Should i use function or procedure?

I used this code to get it but it didn't work.

CREATE DEFINER = `root`@`localhost` PROCEDURE `getUndertime` ( IN `varDatePresent` DATE, IN `varEmpID` VARCHAR( 8 ) ) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER
BEGIN SELECT DAY( LAST_DAY( varDatePresent ) )
INTO @totaldays ;

SET @daycount =0;

WHILE(
@daycount < @totaldays
) DO SELECT Undertime
FROM view_dtr
WHERE EmpID LIKE '%varEmpID%'
AND DatePresent = varDatePresent
INTO @undertime ;

SELECT Overtime
FROM view_dtr
WHERE EmpID LIKE '%varEmpID%'
AND DATE_ADD( DatePresent, INTERVAL 1
DAY )
INTO @overtime ;

SET @totalUndertime = @undertime - @overtime ;

SET @daycount = @daycount +1;

END WHILE;

SELECT @totalUndertime ;

END ;


Any suggestion will help me very much.
Thank You in advance.

Answer

Wouldn't Group By do the trick?

SELECT EMPID, Week(DatePresent) as 'WeekNumber',
SUM(COALESCE(undertime,0)-COALESCE(overtime,0)) as 'TotalUndertime' 
FROM table_name GROUP BY EmpID, Week(DatePresent)

Usage: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

Edit: Based on your definition of TotalUndertime. As a side note, I strongly suggest not to use LIKE when filtering your EMPID.