Add user inputs and outputs in SQL Server

Navigation
-2

I have the following SQL table:


And I would need to get something like this:


That is, add the hours grouped by date, I have tried different options, but not even close to the expected result ...

Failed attempts: 

SELECT  ISNULL(U.usuari, R.idUsuari) AS Usuari, R.IdRegistre, R.IdregistreS, CAST(R.Entrada AS smalldatetime) AS Entrada, CAST(R.Sortida AS smalldatetime) AS Sortida, R.TempsEnHores,  R.TempsEnMinuts
FROM  (SELECT  IdUsuari AS idUsuari, IdRegistre, IdRegistreS AS IdregistreS, ISNULL(Projecte, '') AS Referencia, ISNULL(Traspassat, 0) AS Traspassat, DataRegistre AS Entrada, 
           ISNULL(DataRegistreS, DataRegistre) AS Sortida, FORMAT(DATEDIFF(minute, DataRegistre, ISNULL(DataRegistreS, DataRegistre)) / 60.0, 'N2') AS TempsEnHores, 
           FORMAT(DATEDIFF(second, DataRegistre, ISNULL(DataRegistreS, DataRegistre)) / 60.0, 'N2') AS TempsEnMinuts
        FROM  (SELECT IdUsuari, IdRegistre, Projecte, Traspassat, DataRegistre,
                (SELECT        TOP (1) DataRegistre
                    FROM            vRegistreCorretgit AS V1
                    WHERE        (IdUsuari = V2.IdUsuari) AND (Moviment = 'S') AND (DataRegistre > V2.DataRegistre)
                    ORDER BY DataRegistre) AS DataRegistreS,
                (SELECT        TOP (1) IdRegistre
                    FROM            vRegistreCorretgit AS V1
                    WHERE        (IdUsuari = V2.IdUsuari) AND (Moviment = 'S') AND (DataRegistre > V2.DataRegistre)
                    ORDER BY DataRegistre) AS IdRegistreS
                FROM  vRegistreCorretgit AS V2
            WHERE (Moviment = 'E')) AS Entrades) AS R LEFT  JOIN dbo.Usuaris AS U ON R.idUsuari = U.Id                
WHERE idUsuari = 34

Result:

    
asked by Ivan Ortega 17.07.2018 в 11:06
source

2 answers

0

I understand that what you would like to know is, between inputs and outputs, what was the difference in hours, in decimal format, of what said user did on that date.

If so, and with the example that you put in the table, the following code satisfies the requirement: 

WITH cteHorasE
AS
(
SELECT 
    CONVERT(date, Hora) as Fecha, 
    IDUsuario,
    CONVERT(time, Hora) as HoraE,
    ROW_NUMBER() OVER(ORDER BY Hora ASC) AS IdE
FROM Horarios
WHERE Movimiento = 'E'
),

cteHorasS
AS
(
SELECT 
    CONVERT(date, Hora) as Fecha, 
    IDUsuario,
    CONVERT(time, Hora) as HoraS,
    ROW_NUMBER() OVER(ORDER BY Hora ASC) AS IdS
FROM Horarios
WHERE Movimiento = 'S'
)

SELECT
    CE.Fecha,
    CE.IDUsuario,
    SUM(ROUND(DATEDIFF(MI,CE.HoraE,CS.HoraS)/60.0, 2)) AS Dif_En_Horas
FROM
    cteHorasE CE
INNER JOIN cteHorasS CS ON CE.IDUsuario = CS.IDUsuario AND CE.Fecha = CS.Fecha AND CE.IdE = CS.IdS
GROUP BY
    CE.Fecha,
    CE.IDUsuario

In the previous example, the table is called [Schedules] and it has the fields with the same names that you put in the example.

    
answered by 18.07.2018 / 18:23
source
0

try the following query using CTE and conversions of some columns: 

with cteHoras
as
    (select convert(date, Hora) as Fecha, 
            IDUsuario,
            convert(time, Hora) as Hora
    from Carga )
select Fecha, IDUsuario, sum(datediff(ms, '00:00:00.000',Hora) / 3600000.00) as Horas
from cteHoras
group by Fecha, IDUsuario
    
answered by 17.07.2018 в 18:08
How to enable a textbox in jquery? [closed] How to enable and disable a button in React Native and to disable it to put it in a different color