Início / dba / Perguntas / 36208
Accepted
ms_devel
Asked: 2013-03-08 11:39:25 +0800 CST

Como posso calcular o tempo gasto no trabalho em uma única coluna?

  • 772

Gostaria de calcular a quantidade de tempo gasto trabalhando em linhas de dados que contêm um único campo de data/hora.

A primeira ocorrência em um determinado dia pode ser considerada a hora de início, com a próxima ocorrência sendo a hora de término, alternando para frente e para trás em qualquer dia. Portanto, se eu começar às 9h e almoçar às 12h, voltar do almoço às 12h30 e sair para o dia às 17h, meus dados ficariam assim:

2013-03-07 09:00:00
2013-03-07 12:00:00
2013-03-07 12:30:00
2013-03-07 17:00:00

Aqui está mais uma amostra (na minha localidade, usamos .como separador de data):

22. 2. 2013 9:09:00
22. 2. 2013 13:28:09
25. 2. 2013 9:43:16
25. 2. 2013 15:15:19
25. 2. 2013 19:30:00
25. 2. 2013 21:30:00
26. 2. 2013 8:27:54
26. 2. 2013 16:31:51
27. 2. 2013 8:52:59
27. 2. 2013 15:08:35
28. 2. 2013 8:37:54
28. 2. 2013 16:37:08
28. 2. 2013 18:30:00
28. 2. 2013 20:30:00

Eu gostaria de saber o tempo total gasto no trabalho em um determinado dia e também gostaria de poder contabilizar o tempo gasto, por exemplo, em uma determinada semana ou mês.

sql-server datetime

1 respostas

  1. Best Answer
    DECLARE @d TABLE(dt DATETIME);

INSERT @d VALUES
('2013-02-22 09:09:00'),
('2013-02-22 13:28:09'),
('2013-02-25 09:43:16'),
('2013-02-25 15:15:19'),
('2013-02-25 19:30:00'),
('2013-02-25 21:30:00'),
('2013-02-26 08:27:54'),
('2013-02-26 16:31:51'),
('2013-02-27 08:52:59'),
('2013-02-27 15:08:35'),
('2013-02-28 08:37:54'),
('2013-02-28 16:37:08'),
('2013-02-28 18:30:00'),
('2013-02-28 20:30:00');

;WITH x AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt),
   p = LAG(dt, 1) OVER (ORDER BY dt),
   m = DATEDIFF(MINUTE, LAG(dt, 1) OVER (ORDER BY dt), dt)
 FROM @d
)
SELECT 
  start = p, 
  [end] = e, 
  [minutes_elapsed] = m, 
  [time] = CONVERT(TIME(0), DATEADD(MINUTE, m, 0))
 FROM x
 WHERE r % 2 = 0
 ORDER BY e;

    Se você precisar fazer isso em versões mais antigas (2005 -> 2008 R2):

    ;WITH cte1 AS
(
  SELECT 
   e = dt,
   r = ROW_NUMBER() OVER (ORDER BY dt)
  FROM @d
),
cte2 AS
(
  SELECT 
    x.r,
    [start] = x.e, 
    [end] = y.e, 
    m = DATEDIFF(MINUTE, x.e, y.e)
  FROM cte1 AS x INNER JOIN cte1 AS y
  ON x.r = y.r - 1
  WHERE y.r % 2 = 0
)
SELECT
  [start],
  [end],
  minutes_elapsed = m,
  [time] = CONVERT(CHAR(8), DATEADD(MINUTE, m, 0), 108)
 FROM cte2
 ORDER BY [start];

    O esquema sugerido para registrar o tempo de entrada/saída é bem simples:

    CREATE TABLE dbo.Shifts
(
  EmployeeID INT NOT NULL, -- probably Foreign Key, right?
  ClockIn DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ClockOut DATETIME NULL
);

    Agora sua consulta é:

    SELECT 
  EmployeeID, 
  ClockIn, 
  ClockOut, 
  ShiftLength = CONVERT(TIME(0), DATEADD(MINUTE, 
    DATEDIFF(MINUTE, ClockIn,  
      COALESCE(ClockOut, CURRENT_TIMESTAMP) -- to capture shift in progress
    ), 0))
FROM dbo.Shifts
WHERE ...
    • 9

relate perguntas