Use ALIAS for conditional and operations of addition, subtraction, etc.

Question

Use ALIAS for conditional and operations of addition, subtraction, etc.

Navigation

#1 by (1 votes)

0

I want to compare the ALIAS hours with the ALIAS PlanWorkHours and assign the result to a new variable, however this is not possible since these ALIAS are temporary or do not represent as such a column of the table, there is some way to implement this solution.

if horasMes>PlanWorkHours
HorasExtra=PlanWorkHours-horasMes

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 
             fechaEntrada BETWEEN CONVERT(DATETIME, '2017-11-01 00:00:00',121) and 
                CONVERT(DATETIME,'2017-11-30 23:59:59',121)and c.fkStatus !=1 
            group by
            c.CodeCompany, 
            a.idClave, 
            d.idDepto,
            t.HorasAlMes,
            c.nombre + ' ' + c.apellidos

sql sql-server

asked by Noel L 26.12.2017 в 22:15

source

1 answer

Validate data duplication with data annotation problems configuring apache tomcat 8.5.24 for java

score 1 · Accepted Answer

You can do the calculations for PlanWorkHours and horasMes within a derived table (or also within a common table expression if you prefer), which then allows you to use these 2 values as normal columns in your main query .

Example:

select ...,
       HorasExtra=PlanWorkHours-horasMes -- aquí sí puedes usar PlanWorkHours y horasMes como quieras
  from (
    select ...,
           t.HorasAlMes as PlanWorkHours,
           SUM(DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24) AS horasMes,
           ...
      from ...  
  ) t

Or, using a common table expression:

;with cte as (
    select ...,
           t.HorasAlMes as PlanWorkHours,
           SUM(DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24) AS horasMes,
           ...
      from ...  
)
select ...,
       HorasExtra=PlanWorkHours-horasMes -- aquí sí puedes usar PlanWorkHours y horasMes como quieras
  from cte