Como realizar cálculos com horas no SQL Server – Parte I

Boa Tarde Pessoal,

Depois de uma semana atípica e bem conturbada é hora de atualizar o blog. Recentemente vi uma dúvida de SQL Server que foi a gota d’água. A dúvida referia-se a um cálculo de horas em uma tabela específica. Já perdi a conta de quantas dúvidas referente a cálculos horários eu já vi em fóruns, comunidades e sala de aula. Como "o copo encheu" chegou a hora de deixar a pregüiça de lado e fazer um post dedicado a isso. Antes de propriamente discorrer sobre o problema é necessário elaborar uma hipotética situação para ficar mais fácil de exemplificar.

A empresa XPD Ltda deseja desenvolver um sistema de ponto eletrônico de forma a controlar os horários de entrada e saída de seus funcionários. A captura de dados será biométrica e logo após os funcionários passarem seu polegar direito, o sistema deverá registrar a matrícula do funcionário e o horário exato da entrada (ou saída) do funcionário. Nenhum funcionário está autorizado a "virar a noite" no trabalho e portanto todos os registros irão ocorrer no mesmo dia. O primeiro registro naturalmente será uma "entrada". Os registros subseqüentes serão intercalados de forma que uma saída aparecerá após uma entrada e caso haja uma nova entrada (típico do retorno após o almoço), a nova entrada será registrada logo após a saída posterior. Se houver uma entrada sem uma saída respectiva, considerar-se-á que o horário de saída foi igual ao da última entrada.

Há duas possibilidades de modelagem aqui. Normalmente o número de entradas e saídas de um funcionário tende a ser baixo (ninguém irá entrar e sair na empresa muitas vezes ao dia). É possível modelar de forma um pouco mais inflexível a relação de entradas e saídas deixando-as fixas (ex: até quatro entradas e saídas no dia) ou utilizar uma abordagem um pouco mais flexível deixando esse número variável. Não há nenhum problema com nenhuma das abordagens mas é claro que elas possuem vantagens e desvantagens associadas (inclusive em relação ao horário). Utilizarei ambas.

Colunas de Entrada e Saída Fixas

Nessa abordagem irei adotar a possibilidade de até quatro entradas e quatro saídas por dia por funcionário. O script a seguir representa bem essa situação. Não coloquei as triggers para efetivar as devidas restrições necessárias (até por que além de poder fazer também na aplicação, o foco é explicar o cálculo):

CREATE TABLE tblRegistrosHorarios (
    Matricula CHAR(5),
    Data SMALLDATETIME,
    Entrada1 SMALLDATETIME, Saida1 SMALLDATETIME,
    Entrada2 SMALLDATETIME, Saida2 SMALLDATETIME,
    Entrada3 SMALLDATETIME, Saida3 SMALLDATETIME,
    Entrada4 SMALLDATETIME, Saida4 SMALLDATETIME)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090330’,
‘20090330 08:00’,‘20090330 12:00’,‘20090330 14:00’,‘20090330 18:00’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090331’,
‘20090331 08:00’,‘20090331 12:00’,‘20090331 14:00’,‘20090331 16:00’,
‘20090331 16:45’,‘20090331 19:00’,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090401’,
‘20090401 08:00’,‘20090401 10:13’,‘20090401 11:45’,‘20090401 12:45’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090402’,
‘20090402 09:07’,‘20090402 12:31’,‘20090402 14:11’,‘20090402 18:19’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0001’,‘20090403’,
‘20090403 07:43’,‘20090403 12:37’,‘20090403 13:28’,‘20090403 15:03’,
‘20090403 17:14’,‘20090403 20:38’,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090330’,
‘20090330 08:30’,‘20090330 12:17’,‘20090330 13:32’,‘20090330 16:21’,
‘20090330 17:53’,‘20090330 18:30’,‘20090330 19:30’,‘20090330 20:30’)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090331’,
‘20090331 07:51’,‘20090331 12:03’,‘20090331 13:58’,‘20090331 18:00’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090401’,
‘20090401 08:00’,‘20090401 10:13’,‘20090401 11:45’,‘20090401 12:45’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090402’,
‘20090402 08:37’,‘20090402 11:31’,‘20090402 13:24’,‘20090402 19:19’,
NULL,NULL,NULL,NULL)

INSERT INTO tblRegistrosHorarios VALUES (‘M0002’,‘20090403’,
‘20090403 09:32’,‘20090403 12:49’,‘20090403 13:51’,‘20090403 17:00’,
‘20090403 17:45’,‘20090403 19:38’,NULL,NULL)

O primeiro passo seria calcular as diferenças horárias para posteriormente fazer uma soma. Ex:

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios)

SELECT * FROM MinutosDia

Essa instrução SQL retorna os dados normais da tabela além das diferenças entre as entradas e saídas horárias. Como existem até 4 entradas e até quatro saídas, podem existir até quatro diferenças. Posteriormente basta somar as diferenças.

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios)

SELECT Matricula, Data, Dif1 + Dif2 + Dif3 + Dif4 As TotalMinutos
FROM MinutosDia

Com mais um pequeno ajuste o GROUP BY pode ser perfeitamente aplicado.

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios)

SELECT Matricula, Data, SUM(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
FROM MinutosDia
GROUP BY Matricula, Data
 

Chegamos a conclusão de que no período previsto, o empregado M0001 possui 2213 minutos e o empregado M0002 possui 2208 minutos. Normalmente não é desejável exibir o total de minutos, mas sim a quantidade horária em um formato hh:mm por exemplo. Sabendo que uma hora equivale a 60 minutos, podemos dividir o total em minutos e calcular o total de horas e a diferença será o total de minutos que sobraram. Ex:

;WITH MinutosDia As (
SELECT Matricula,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios),

Resumo As (
SELECT Matricula, SUM(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
FROM MinutosDia
GROUP BY Matricula)

SELECT Matricula,
    CAST((TotalMinutos / 60) As VARCHAR(2)) + ‘:’ +
    CAST((TotalMinutos % 60) As VARCHAR(2)) As CargaHoraria
FROM Resumo

Dessa forma é possível calcular que M0001 realizou o total de 36:53 e M0002 realizou o total de 36:48. Podemos também visualizar os lançamentos de forma diária acrescentando a coluna Data no GROUP BY.

;WITH MinutosDia As (
SELECT Matricula, Data,
    Entrada1, Saida1, Entrada2, Saida2,
    Entrada3, Saida3, Entrada4, Saida4,
    ISNULL(DATEDIFF(Mi,Entrada1,Saida1),0) As Dif1,
    ISNULL(DATEDIFF(Mi,Entrada2,Saida2),0) As Dif2,
    ISNULL(DATEDIFF(Mi,Entrada3,Saida3),0) As Dif3,
    ISNULL(DATEDIFF(Mi,Entrada4,Saida4),0) As Dif4
FROM tblRegistrosHorarios),

Resumo As (
SELECT Matricula, Data, SUM(Dif1 + Dif2 + Dif3 + Dif4) As TotalMinutos
FROM MinutosDia
GROUP BY Matricula, Data)

SELECT Matricula, Data,
    CAST((TotalMinutos / 60) As VARCHAR(2)) + ‘:’ +
    CAST((TotalMinutos % 60) As VARCHAR(2)) As CargaHoraria
FROM Resumo

Para evitar possíveis problemas de formatação, basta adaptar a última consulta

SELECT Matricula, Data,
    RIGHT(‘0’ + CAST((TotalMinutos / 60) As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST((TotalMinutos % 60) As VARCHAR(2)),2) As CargaHoraria
FROM Resumo

Lançamentos de horas variáveis

Na abordagem anterior (típica de um formulário de folha de ponto) existem alguns inconvenientes como a limitação de entradas e saídas e principalmente a quantidade de registros nulos. Utilizarei os mesmos exemplos porém com uma modelagem conceitualmente mais adequada.

CREATE TABLE tblRegHorarios (
    Matricula CHAR(5),
    Data SMALLDATETIME,
    Horário SMALLDATETIME,
    Tipo CHAR(1))

Ao invés de fazer as várias instruções de INSERT INTO, pode-se converter as colunas da tabela anterior em linhas e realizar uma carga através do operador UNPIVOT e do script abaixo:

;WITH LancamentosHorarios As (
SELECT *
FROM
   (SELECT Matricula, Data, Entrada1, Saida1, Entrada2,
    Saida2, Entrada3, Saida3, Entrada4, Saida4
   FROM tblRegistrosHorarios) PV
UNPIVOT
   (Horario FOR Lancamento IN
      (Entrada1, Saida1, Entrada2, Saida2,
        Entrada3, Saida3, Entrada4, Saida4)
) As UP)

INSERT INTO tblRegHorarios
SELECT
    Matricula, Data, Horario,
    LEFT(Lancamento,1)
FROM LancamentosHorarios

SELECT Matricula, Data, Horario, Tipo FROM tblRegHorarios

Agora que a tabela tblRegHorarios está devidamente populada, os cálculos são um pouco mais trabalhosos. Na situação anterior, basta somar consultas. Na situação atual é preciso somar as linhas agrupadas por matricula e data ou somente por matrícula. A primeira vista, pode parecer que um simples SUM com o GROUP BY possa ser suficiente, mas dessa vez não é assim tão simples. Será necessário calcular a diferença entra a 2ª e a 1ª ou ainda entre a 4ª e a 3ª, a 6ª e a 5ª e assim sucessivamente. Existem algumas formas de se fazer isso, mas vamos a uma mais simples.

Sabendo que as entradas antecedem as saídas podemos deduzir que as entradas ocuparam sempre uma posição ímpar e as saídas uma posição par. Um outro detalhe é que a saída está sempre referente a sua entrada anterior. Se por exemplo uma saída representa a 4º registro então podemos afirmar que o 3º será sua entrada correspondente. Para numerar os registros, a função ROW_NUMBER() é bem adequada. Como a numeração deve ser por funcionário e por dia, é necessário usar o Partition By. Caso essas funções e operadores não sejam muito familiares, recomendo o artigo ?

SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios

Agora que existe uma coluna numerada, basta combinar as saídas pares de suas entradas ímpares correspondentes.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios)

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1

Essa consulta reproduzirá todos os lançamentos de entrada e saída por funcionário e por data. O JOIN entre matricula e data faz sentindo, mas alguns certamente devem estar se perguntando a razão de L1.Pos = L2.Pos – 1 e principalmente L1.Pos % 2 =1. O primeiro predicado é para combinar os registros com seu subseqüente (ex: 1º registro com o 2º registro, o 3º registro com o 4º registro, etc). O segundo predicado é para garantir que os registros em L1 serão ímpares (o resto de uma divisão de um número ímpar será sempre igual a um). Sem essa condição, seria admitido combinar o 2º registro com o 3º registro e a combinação não ajuda com o resultado.

Com o resultado de todas as entradas e saídas é possível utilizar a função DateDiff normalmente e calcular as diferenças.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios),

LancamentosOrganizados As (

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1)

SELECT Matricula, Data, DateDiff(Mi,Entrada,Saida) As Dif
FROM LancamentosOrganizados

Essa consulta retorna exatamente a diferença entre cada registro de entrada e saída correspondente. Para obter um relatório mais sumarizado, basta apenas substituir a última consulta (a executada contra a CTE LancamentosOrganizados) pela consulta abaixo

SELECT Matricula, SUM(DateDiff(Mi,Entrada,Saida)) As CargaHoraria
FROM LancamentosOrganizados GROUP BY matricula

Obtem-se exatamente o mesmo resultado, ou seja, o empregado M0001 possui 2213 minutos e o empregado M0002 possui 2208 minutos. Pelas mesmas razões da abordagem anterior, é interessante formatar o resultado. O exemplo abaixo mostra o resultado no formato hh:mm e o cálculo realizado por matrícula e por data.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) As Pos
FROM tblRegHorarios),

LancamentosOrganizados As (

SELECT
    L1.Matricula, L1.Data,
    L1.Horario As Entrada, L2.Horario As Saida
FROM
    LancamentosHorarios As L1
    INNER JOIN LancamentosHorarios As L2 ON
        L1.Matricula = L2.Matricula AND L1.Data = L2.Data AND
        L1.Pos = L2.Pos – 1 AND L1.Pos % 2 = 1)

SELECT Matricula, Data,
    RIGHT(‘0’ + CAST(SUM(DateDiff(Mi,Entrada,Saida)) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(SUM(DateDiff(Mi,Entrada,Saida)) % 60 As VARCHAR(2)),2)
As CargaHoraria
FROM LancamentosOrganizados GROUP BY Matricula, Data

Essa consulta retorna os dados horários devidamente formatados (o GROUP BY pode ser modificado se o resultado for apenas por funcionário). O raciocínio até então foi apenas para "exercitar", no próximo artigo mostrarei outras formas de resolver esse problema além de outras considerações quando os registros estão dispostos em linhas e não em colunas. Para aqueles que querem mostrar o resultado em um formato de colunas, segue o comando de PIVOT.

;WITH LancamentosHorarios As (
SELECT
    Matricula, Data, Horario, Tipo,
    (ROW_NUMBER() OVER (
        PARTITION BY Data, Matricula
        ORDER BY Data, Matricula, Horario) + 1) / 2 As Pos
FROM tblRegHorarios),

Lancamentos As (

SELECT DISTINCT Matricula, Data FROM LancamentosHorarios)

SELECT Matricula, Data,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 1) As Entrada1,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 1) As Saida1,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 2) As Entrada2,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 2) As Saida2,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 3) As Entrada3,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 3) As Saida3,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘E’ AND Pos = 4) As Entrada4,
    (SELECT Horario FROM LancamentosHorarios WHERE
        L.Matricula = Matricula AND L.Data = Data AND
        Tipo = ‘S’ AND Pos = 4) As Saida4

FROM Lancamentos As L

Eu poderia ter pensado em utilizar o operador PIVOT para transformar linhas em colunas desfazendo a operação de UNPIVOT. Infelizmente o PIVOT só transforma linhas em colunas quando há operações de agregação. A transformação direta de linhas em colunas sem nenhuma função de agregação não é possível com o operador PIVOT. O Books OnLine é bem claro quanto a isso.

Até o próximo artigo…

[ ]s,

Gustavo

5 Respostas para “Como realizar cálculos com horas no SQL Server – Parte I

  1. Excelente, muito bom. Conseguiu tirar todas as minhas dúvidas quanto operações com data, utilizando o SQL SERVER.

  2. Leonardo Cristiano de Alice

    Gustavo, você está de parabéns por esse blog.
    Já sanei muitas dúvidas lendo os seus textos.
    Sobre esse, tenho uma dúvida relacioanda a um recurso do SQL Server. Vi que em alguns scripts você usa a sintaxe ;WITH…. Como se chama esse recurso?
    Pelo que eu entendi, trata-se de uma tabela temporária. Achei muito interessante.
    Tem algum link ou explicação para me passar?
    e-mail: leonardo@frisokar.com.br
    Desde já agradeço

  3. Belarmino Nicolau Monteiro Simão

    Gustavo parabéns ….
    eu sou novo em SQL SERVER e estou aprender muitas coisas com o teu blog…
    so gostaria de saber mas sobre o uso do INSERT … SELECT
    tenho muita dificuldade em inserir dados em mais de uma tabela num unico comando…

    abraços!

    • Olá Belarmino,

      Seja bem vindo ao meu blog e ao mundo de banco de dados.

      O INSERT só pode inserir dados em uma tabela por vez. Mesmo que você rode um INSERT com SELECT e o SELECT faça referência à várias tabelas, a tabela “alvo” do INSERT é só uma, pois, não é possível inserir em múltiplas tabelas com um único comando.

      [ ]s,

      Gustavo

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s