This report calculate the hours worked from Monday to Friday of this week.
select U.FULL_NAME AS ATENDENTE, HD_QUEUE.NAME AS FILA,
SUM(format(time_to_sec(timediff(HD_WORK.STOP,HD_WORK.START))/ 3600.0 + HD_WORK.ADJUSTMENT_HOURS,2)) AS 'Horas trabalhadas',
curdate() as Semana,
date_sub(curdate(),INTERVAL (dayofweek(curdate())- 2) day) as Segunda,
date_add(date_sub(curdate(),INTERVAL (dayofweek(curdate())- 2) day), INTERVAL 4 day) as Sexta
from HD_TICKET
LEFT JOIN USER U ON (HD_TICKET.OWNER_ID = U.ID)
left join HD_WORK ON (HD_TICKET.ID = HD_WORK.HD_TICKET_ID)
left join HD_QUEUE ON (HD_TICKET.HD_QUEUE_ID = HD_QUEUE.ID),
(select date_sub(curdate(),INTERVAL (dayofweek(curdate())- 2) day) as LUNES,
date_add(date_sub(curdate(),INTERVAL (dayofweek(curdate())- 2) day), INTERVAL 4 day) as VIERNES FROM DUAL) H
WHERE U.ID = 29 and
HD_TICKET.OWNER_ID = U.ID and
HD_TICKET.ID = HD_WORK.HD_TICKET_ID and
HD_TICKET.MODIFIED between H.LUNES and H.VIERNES
GROUP BY ATENDENTE
Comments