CASE judgments

0

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;
    
asked by Noel L 03.01.2018 в 21:09
source

1 answer

2

If your intention is to define 2 expressions CASE for 2 separate "columns", which seems to be your case, you just need to make sure that each expression CASE ends correctly with a END :

horasExtra=
case 
    when horasMes>PlanWorkHours then abs(PlanWorkHours-horasMes)
end, -- te falta esto.............

horasFaltantes=
case 
    when horasMes<PlanWorkHours then abs(PlanWorkHours-horasMes)
end
    
answered by 03.01.2018 / 21:13
source