How one more case is added to do the following:
horasExtra=
case
when horasMes>PlanWorkHours then abs(PlanWorkHours-horasMes)
horasFaltantes=
case
when horasMes<PlanWorkHours then abs(PlanWorkHours-horasMes)
end
In the current query it is only valid with a case, then in which way you can add multiple CASE
with cte as (
select DISTINCT c.CodeCompany AS CompanyCode,
CONVERT(char(6), GETDATE(), 112) AS Period,
a.idClave as EmployeID,
c.nombre + ' ' + c.apellidos as EmployeName,
d.idDepto as Depto,
t.HorasAlMes as PlanWorkHours,
SUM(DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24) AS horasMes,
abs(t.HorasAlMes-SUM(DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24)) as Resta
from tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave
JOIN tblDepto d ON c.fkDepto=d.idDepto
JOIN tblTurno t ON c.fkTurno=t.idTurno
where --año-mes-día
fechaEntrada BETWEEN CONVERT(DATETIME, '2017-12-01 00:00:00',121) and
CONVERT(DATETIME,'2017-12-31 23:59:59',121)and c.fkStatus !=1
group by
c.CodeCompany,
a.idClave,
d.idDepto,
t.HorasAlMes,
c.nombre + ' ' + c.apellidos
)
select CompanyCode, Period, EmployeID, EmployeName,Depto,PlanWorkHours,horasMes,
horasExtra=
case
when horasMes>PlanWorkHours then abs(PlanWorkHours-horasMes)
end
from cte;