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

Boa Tarde Pessoal,

No artigo anterior, demonstrei como realizar cálculos com horas no SQL Server. Havia duas abordagens, uma baseada em colunas e outra baseada em linhas. Nesse artigo continuarei a explorar o exemplo anterior baseado em linhas bem como mostrar outras soluções e considerações em relação ao SQL Server 2008. Segue o script a ser utilizado (caso as tabelas do artigo anterior não estejam mais disponíveis):

CREATE TABLE tblRegHorarios (
    Matricula CHAR(5),
    Data SMALLDATETIME,
    Horario SMALLDATETIME,
    Tipo CHAR(1))

INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 12:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 14:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090330’,‘20090330 18:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 12:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 14:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 16:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 16:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090331’,‘20090331 19:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 10:13’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 11:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090401’,‘20090401 12:45’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 09:07’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 12:31’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 14:11’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090402’,‘20090402 18:19’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 07:43’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 12:37’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 13:28’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 15:03’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 17:14’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0001’,‘20090403’,‘20090403 20:38’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 08:30’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 12:17’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 13:32’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 16:21’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 17:53’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 18:30’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 19:30’,‘E’)

INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090330’,‘20090330 20:30’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 07:51’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 12:03’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 13:58’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090331’,‘20090331 18:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 08:00’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 10:13’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 11:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090401’,‘20090401 12:45’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 08:37’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 11:31’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 13:24’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090402’,‘20090402 19:19’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 09:32’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 12:49’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 13:51’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 17:00’,‘S’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 17:45’,‘E’)
INSERT INTO tblRegHorarios VALUES (‘M0002’,‘20090403’,‘20090403 19:38’,‘S’)

A solução em TSQL apontada foi.

;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, SUM(DateDiff(Mi,Entrada,Saida)) As CargaHoraria
FROM LancamentosOrganizados GROUP BY matricula

A solução é interessante e o raciocínio também não é difícil. Como disse era apenas para exercitar, pois, embora uma boa solução ela é trabalhosa e um pouco dispendiosa. Essa solução foi elaborada dessa forma, pois, não havia como realizar SUM diretamente contras as colunas de data. Outra limitação que levou a essa solução é que o DateDiff não pode ser aplicado diretamente a linhas diferentes.

Há uma outra solução em TSQL mais simples e menos dispendiosa. Essa solução depende de um simples raciocínio matemático:

Se C – B = A então (C + Y) – (B + Y) = A já que C + Y – B – Y = C – B

Tudo o que fiz foi adicionar um valor Y às variáveis C e B sem desequilibrar a equação. É como se eu perguntasse qual a diferença de idade entre o SQL Server e o ORACLE. Hoje a diferença entre eles é de dez anos (o Oracle data de 1979 e o SQL Server de 1989). Se eu fizer essa pergunta daqui a 5, 10 ou 15 anos, a resposta será sempre a mesma, ou seja, independente de quando a pergunta é feita, a diferença será sempre de dez anos. Qual seria a relação desse raciocínio com o cálculo das horas ? Vejamos o resultado da consulta abaixo:

SELECT Matricula, Data, Horario, Tipo,
DateDiff(MI,Data,Horario) As Dif
FROM tblRegHorarios
WHERE Matricula = ‘M0001’ AND Data = ‘20090330’

O resultado tabular é expresso abaixo:

Matrícula

Data

Horário

Tipo

Dif

M0001

2009-03-30 00:00:00

2009-03-30 08:00:00

E

480

M0001

2009-03-30 00:00:00

2009-03-30 12:00:00

S

720

M0001

2009-03-30 00:00:00

2009-03-30 14:00:00

E

840

M0001

2009-03-30 00:00:00

2009-03-30 18:00:00

S

1080

A diferença entre "2009-03-30 00:00" e "2009-03-30 08:00" é de 480 minutos. A diferença entre "2009-03-30 00:00" e "2009-03-30 12:00" é de 720 minutos. A idéia é subtrair "2009-03-30 08:00" de "2009-03-30 12:00" que totaliza 4 horas. A diferença entre a primeira entrada e a primeira saída será sempre de 4 horas independente se há ou não uma subtração ou adição para desses dois momentos. É um pouco abstrato admitir uma data negativa, mas se os resultados estão expressos como inteiros (o retorno do DateDiff) então pode-se fazer uma subtração. Ex:

SELECT Matricula, Data, Horario, Tipo,
CASE Tipo WHEN ‘S’ THEN DateDiff(MI,Data,Horario)
ELSEDateDiff(MI,Data,Horario) END As Dif
FROM tblRegHorarios
WHERE Matricula = ‘M0001’ AND Data = ‘20090330’

O resultado tabular é expresso abaixo:

Matrícula

Data

Horário

Tipo

Dif

M0001

2009-03-30 00:00:00

2009-03-30 08:00:00

E

-480

M0001

2009-03-30 00:00:00

2009-03-30 12:00:00

S

720

M0001

2009-03-30 00:00:00

2009-03-30 14:00:00

E

-840

M0001

2009-03-30 00:00:00

2009-03-30 18:00:00

S

1080

A consulta negativou a diferença quando se trata de uma entrada, agora o cálculo fica bem mais simples. Ex:

;WITH LancamentosHorarios As (
    SELECT Matricula, Data, Horario, Tipo,
    CASE Tipo WHEN ‘S’ THEN DateDiff(MI,Data,Horario)
    ELSEDateDiff(MI,Data,Horario) END As Dif
    FROM tblRegHorarios)

SELECT Matricula, Data, SUM(Dif) As Dif
FROM LancamentosHorarios GROUP BY Matricula, Data

Para um resultado mais apresentável, pode-se evoluir o código:

;WITH LancamentosHorarios As (
    SELECT Matricula, Data, Horario, Tipo,
    CASE Tipo WHEN ‘S’ THEN DateDiff(MI,Data,Horario)
    ELSEDateDiff(MI,Data,Horario) END As Dif
    FROM tblRegHorarios)

SELECT Matricula,
    RIGHT(‘0’ + CAST(SUM(Dif) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(SUM(Dif) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM LancamentosHorarios GROUP BY Matricula

A consulta (bem mais simples) retorna que o funcionário M0001 possui 36:53 horas trabalhadas e o funcionário M0002 possui 36:48 horas trabalhadas. Com isso são apresentadas duas soluções baseadas em uma única instrução SELECT mas que outras alternativas existem ? Pode-se pensar em um cursor, mas gostaria de abordar algo mais moderno.

O uso do CLR

Já demonstrei o uso do CLR em alguns outros artigos como a utilização de matrizes e importação de arquivos XML. Nessas ocasiões observou-se que o CLR pode ser usado para criar tipos de dados ou stored procedures, mas além disso podemos utilizá-los para criar funções de agregação. Não entrarei nos detalhes do CLR, mas os interessados podem pesquisar em Como e quando programar em CLR no Microsoft SQL Server 2005.

O trecho de código abaixo representa um Assembly para criar uma função de agregação.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
    Format.Native, IsInvariantToNulls = true,
    IsInvariantToDuplicates = false,
    IsInvariantToOrder = false)]

public struct SomaTempo
{
    private SqlDateTime DataAnterior;
    private SqlDateTime DataAtual;
    private int Minutos;
    private bool PrimeiroRegistro;
    private int Pos;

    public void Init()
    {
        // Inicializa as variáveis
        this.Minutos = 0;
        this.Pos = 1;
        this.PrimeiroRegistro = true;
    }

    public void Accumulate(SqlDateTime value)
    {
        DataAtual = value;

        // Acumula o valor se não for nulo
        if (value.IsNull)
        {
            return;
        }

        if (PrimeiroRegistro)
        {
            DataAnterior = DataAtual;
        }

        PrimeiroRegistro = false;

        // Calcula a diferença apenas se o registro for uma saída e o anterior uma entrada
        if (Pos % 2 == 0)
        {
            TimeSpan ts = DataAtual.Value.Subtract(DataAnterior.Value);
            this.Minutos += (ts.Hours * 60) + (ts.Minutes);
        }

        Pos += 1;
        DataAnterior = DataAtual;
    }

    public void Merge(SomaTempo Group)
    {
        Minutos += Group.Minutos;
    }

    public SqlInt32 Terminate()
    {
        // Retorna o valor acumulado
        return this.Minutos;
    }
}

Essa function receberá uma entrada do tipo SmallDatetime. O primeiro registro a ser informado será um registro de entrada, o segundo de saída, o terceiro de entrada, etc. Sempre que o registro for uma saída, é computada a diferença entre minutos em relação ao registro anterior. Para não haver dependência do Visual Studio, o procedimento para compilar esse código envolve os seguintes passos:

  • Copiar e colar o código no bloco de notas
  • Salvar o arquivo como CalculaSomaData.cs em algum diretório (ex: G:\CLR\)
  • Abrir um prompt de comando
  • Navegar até o diretório C:\Windows\Microsoft.NET\Framework\V2.0.50727 (ou superior se desejar)
  • Rodar o comando csc /target:library G:\CLR\CalculaSomaData.cs (supondo que o arquivo esteja em G:\CLR\)
  • Localizar o arquivo CalculaSomaData.dll na pasta utilizada no prompt
  • Mover a DLL para um local mais apropriado (ex: G:\CLR\)

O próximo passo é registrar o ASSEMBLY para que o código possa ser utilizado e posteriormente a criação da agregação.

— Cria o Assembly
CREATE ASSEMBLY CalculaSomaData FROM ‘G:\CLR\CalculaSomaData.dll’

— Cria a agregação
CREATE AGGREGATE CalculaMinutos(@Data SmallDateTime)
RETURNS INT
EXTERNAL NAME
[CalculaSomaData].[SomaTempo];

Uma vez que a agregação tenha sido criada, basta utilizá-la:

— Utiliza a agregação (cálculo por dia)
SELECT Matricula, Data,
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM tblRegHorarios GROUP BY Matricula, Data

— Utiliza a agregação (cálculo total)
SELECT Matricula,
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(dbo.CalculaMinutos(Horario) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM tblRegHorarios GROUP BY Matricula

A agregação em CLR poderia ser customizada para retornar já no formato hh:mm, mas isso talvez a tornasse um pouco inflexível, embora seja possível (basta alterar o método Terminate).

Mas e se fosse no SQL Server 2008 com o tipo de dados TIME ?

O SQL Server 2008 introduziu o tipo de dados TIME que armazena um horário diretamente, mas será que ele realmente pode realmente auxiliar ? O script abaixo adapta os dados ao SQL Server 2008.

— Cria uma tabela com o tipo TIME
CREATE TABLE tblRegHorarios2008 (
    Matricula CHAR(5), Data SMALLDATETIME,
    Horario TIME(0), Tipo CHAR(1))

— Insere os registros da tabela anterior
INSERT INTO tblRegHorarios2008 (Matricula, Data, Horario, Tipo)
SELECT Matricula, Data, CAST(Horario As TIME), Tipo FROM tblRegHorarios

A consulta abaixo possui uma semântica para calcular a carga horária de cada funcionário:

SELECT Matricula, Data,
    SUM(CASE Tipo WHEN ‘S’ THEN Horario
    ELSE -Horario END) As Total
FROM tblRegHorarios2008
GROUP BY Matricula, Data

A lógica seria que se o registro for de entrada, a hora seria negativada e se o registro fosse de saída a hora seria exibida normalmente. Assim pode-se posteriormente efetuar a somatória dos horários. Embora a lógica faça sentido, sua execução retorna uma mensagem de erro.

Msg 8117, Level 16, State 1, Line 3
Operand data type time is invalid for minus operator.

Pode-se contornar essa mensagem, refazendo-se a consulta.

SELECT Matricula, Data,
    SUM(CASE Tipo WHEN ‘S’ THEN Horario ELSE ’00:00′ END) –
    SUM(CASE Tipo WHEN ‘E’ THEN Horario ELSE ’00:00′ END)
As Total FROM tblRegHorarios2008
GROUP BY Matricula, Data

A lógica seria se o registro for de saída, a hora é exibida e sumarizada e posteriormente substraí-se o somatório das entradas. Embora também faça sentido, se executada também incorre em um erro.

Msg 8117, Level 16, State 1, Line 2
Operand data type time is invalid for sum operator.

Isso mostra que o tipo de dados TIME pode ajudar na melhor escolha dos tipos de dados, mas infelizmente a impossibilidade de sumarizá-los não representa grandes mudanças em relação às versões anteriores. O mecanismo da diferença é uma boa solução. Ex:

SELECT Matricula, Data, Horario, Tipo,
CASE Tipo WHEN ‘S’ THEN DateDiff(MI,’00:00′,Horario)
ELSEDateDiff(MI,’00:00′,Horario) END As Dif
FROM tblRegHorarios2008
WHERE Matricula = ‘M0001’ AND Data = ‘20090330’

O resultado tabular é expresso abaixo:

Matrícula

Data

Horário

Tipo

Dif

M0001

2009-03-30 00:00:00

2009-03-30 08:00:00

E

-480

M0001

2009-03-30 00:00:00

2009-03-30 12:00:00

S

720

M0001

2009-03-30 00:00:00

2009-03-30 14:00:00

E

-840

M0001

2009-03-30 00:00:00

2009-03-30 18:00:00

S

1080

A consulta é semelhante a presente no início do arquivo e utiliza a mesma lógica porém a referência agora é para o tipo de dados TIME. Uma vez que os minutos possam ser calculados, a montagem da carga horária também é possível.

;WITH LancamentosHorarios As (
    SELECT Matricula, Data, Horario, Tipo,
    CASE Tipo WHEN ‘S’ THEN DateDiff(MI,’00:00′,Horario)
    ELSEDateDiff(MI,’00:00′,Horario) END As Dif
    FROM tblRegHorarios2008)

SELECT Matricula,
    RIGHT(‘0’ + CAST(SUM(Dif) / 60 As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST(SUM(Dif) % 60 As VARCHAR(2)),2) As CargaHoraria
FROM LancamentosHorarios GROUP BY Matricula

Os resultados mostram que a carga horária do funcionário M0001 é de 36:53 e do funcionário M0002 é de 36:48.

Não foi meu objetivo explorar a questão de cálculos horários no SQL Server. Há mais algumas situações que não foram abordadas até então e que também são comuns. Mostrarei uma dessas situações no próximo artigo. Até lá…

[ ]s,

Gustavo

2 Respostas para “Como realizar cálculos com horas no SQL Server – Parte II

  1. Robson Gomes Nascimento

    Gustavo, muito bom esse artigo sobre datas!

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