instruccion - mssql alter table

Cómo resumir el campo de tiempo en SQL Server

Question

Tengo una columna llamada "WrkHrs" y el tipo de datos es time (hh: mm: ss). Quiero resumir las horas de trabajo de los empleados. Pero como es hora de que el tipo de datos del servidor SQL no me permita usar como sum(columnname) .

¿Cómo puedo resumir el tipo de datos de tiempo fieled en consultas SQL?

Answer 1

Para MS SQL Server, cuando su Tiempo de trabajo se almacena como una hora, o un varchar para resumirlo, debe considerar que:

1) El formato de hora no es una suma compatible, por lo que necesita analizarla

2) 23: 59: 59.9999999 es el valor máximo para el tiempo.

Entonces, el código que funcionará para obtener el número total de WorkingHours: WorkingMinutes: WorkingSeconds sería el siguiente:

SELECT 
 CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) / 3600),''00'') as varchar(max)) + '':'' + 
  CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) % 3600 / 60),''00'') as varchar(max)) + '':'' + 
  CAST(FORMAT((SUM((DATEPART("ss",WorkingTime) + DATEPART("mi",WorkingTime) * 60 + DATEPART("hh",WorkingTime) * 3600)) % 3600 % 60),''00'') as varchar(max)) as WorkingTimeSum
FROM TableName

Answer 2

Para resumir las horas de trabajo de un empleado, puede calcular la diferencia entre la hora de inicio y finalización del turno en minutos y convertirla a un formato legible de la siguiente manera:

    DECLARE @StartTime      datetime = ''08:00''
    DECLARE @EndTime        datetime = ''10:47''
    DECLARE @durMinutes     int
    DECLARE @duration       nvarchar(5)

    SET @durMinutes = DATEDIFF(MINUTE, @StartTime, @EndTime)

    SET @duration = 
    (SELECT RIGHT(''00'' + CAST((@durMinutes / 60) AS VARCHAR(2)),2) + '':'' + 
            RIGHT(''00'' + CAST((@durMinutes % 60) AS VARCHAR(2)), 2))

    SELECT @duration

El resultado: 02:47 dos horas y 47 minutos.

Answer 3

DECLARE @Tab TABLE
(
    data CHAR(5)
)

INSERT @Tab
SELECT ''25:30'' UNION ALL
SELECT ''31:45'' UNION ALL
SELECT ''16:00''

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, 
    ''19000101''), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM (
    SELECT ABS(SUM(CASE CHARINDEX('':'', data) WHEN 0 THEN 0 ELSE 3600 * 
        LEFT(data, CHARINDEX('':'', data) - 1) END)) AS theHours,
    ABS(SUM(CASE CHARINDEX('':'', data) WHEN 0 THEN 0 ELSE 60 * 
        SUBSTRING(data, CHARINDEX('':'', data) + 1, 2) END)) AS theMinutes
    FROM @Tab
) AS d

Answer 4

SELECT EmployeeID, minutes_worked = SUM(DATEDIFF(MINUTE, ''0:00:00'', WrkHrs)) 
FROM dbo.table 
-- WHERE ...
GROUP BY EmployeeID;

Puedes formatearlo bastante en la parte frontal. O en T-SQL:

;WITH w(e, mw) AS
(
    SELECT EmployeeID, SUM(DATEDIFF(MINUTE, ''0:00:00'', WrkHrs)) 
    FROM dbo.table 
    -- WHERE ...
    GROUP BY EmployeeID
)
SELECT EmployeeID = e,
  WrkHrs = RTRIM(mw/60) + '':'' + RIGHT(''0'' + RTRIM(mw%60),2)
  FROM w;

Sin embargo, está utilizando el tipo de datos incorrecto. TIME se usa para indicar un punto en el tiempo, no un intervalo o duración. ¿No tendría sentido almacenar sus horas de trabajo en dos columnas distintas, StartTime y EndTime ?