Add the total hours worked during 1 month

Question

Add the total hours worked during 1 month

Navigation

#1 by (2 votes)

1

How can I add the hours in a period of 1 month, starting from an Initial date and a Final date in such a way that the result is as follows:

Code    fechaActual   idClave   nombre       HorasTotalesMes    

2300      2017-12        14290        NombreUsuario             37

The current query that I am executing is the following:

select c.CodeCompany,
    CONVERT(char(6), GETDATE(), 112) AS fechaActual,
    a.idClave,
    c.nombre + ' ' + c.apellidos as Nombre,
    d.Descripcion as Depto, 
   CONVERT(VARCHAR(20),fechaEntrada,108) AS fechaEntrada,
   CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
   DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS sumaHoras

        from tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave
             JOIN tblDepto d ON c.fkDepto=d.idDepto

         where 

            fechaEntrada BETWEEN CONVERT(DATETIME, '2017-11-01 00:00:00',103) and 
            CONVERT(DATETIME,'2017-11-30 23:59:59',103)

However, this query shows them in the following way:

Code fechaActual idClave nombre                         HorasTotalesMes 
2300  201712  14290  User3  14:03:36    21:35:11             7
2300  201712  14290  User3  13:43:33    21:36:34             8
2300  201712  14290  User3  13:56:38    21:02:15             8
2300  201712  14290  User3  13:54:01    21:37:26             8
2300  201712  14290  User3  06:00:00    00:00:00             6

sql sql-server

asked by Noel L 20.12.2017 в 20:23

source

1 answer

How can I add a second navigation drawer? How to change the color of the Toolbar menu icon on Android? [duplicate]

score 2 · Accepted Answer

Using the group by:

select 
    c.CodeCompany,
    CONVERT(char(6), GETDATE(), 112) AS fechaActual,
    a.idClave,
    c.nombre + ' ' + c.apellidos as Nombre,
    d.Descripcion as Depto, 
    --CONVERT(VARCHAR(20),fechaEntrada,108) AS fechaEntrada,
    --CONVERT(VARCHAR(20),fechaSalida,108) AS fechaSalida,
    sum(DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24) AS sumaHoras
from tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave
     JOIN tblDepto d ON c.fkDepto=d.idDepto
where fechaEntrada BETWEEN CONVERT(DATETIME, '2017-11-01 00:00:00',103) and 
      CONVERT(DATETIME,'2017-11-30 23:59:59',103)
group by c.CodeCompany
      , CONVERT(char(6)
      , GETDATE(), 112)
      , a.idClave
      , c.nombre + ' ' + c.apellidos as Nombre
      , d.Descripcion

Something like that, you add SUM in the field that you want to summarize and group by all the other fields, in the case of the date fields that they give different, if it is necessary to remove them because otherwise the group will not work, because they are different all.

The group works to group equal fields ...