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

Boa Tarde Pessoal,

No último artigo abordei mais uma situação envolvendo cálculos horários no SQL Server. Creio que os três primeiros artigos relacionados a esse assunto já sejam subsídio suficiente para boa parte das dúvidas que aparecem nos fóruns, grupos de usuários e comunidades. Na parte final desse tema, farei uso do CLR para resolver algumas das situações postadas no último artigo. A idéia não é resolver os problemas com o CLR já que o TSQL é uma melhor alternativa para os exemplos abordados até então. O objetivo é exemplificar algumas utilizações do CLR (dedico aos meus alunos que sempre reclamam que os MOCs não tem algo muito prático nesse assunto). Não vou discorrer a respeito do que seja o CLR (isso já foi tratado em vários Webcasts e artigos anteriores). Quem desejar conhecer mais sobre CLR, recomendo uma leitura do artigo "Como e quando programar em CLR no Microsoft SQL Server 2005". Também já postei alguns outros artigos que utilizam o CLR ("Utilização de Matrizes", "Importação de Arquivos XML" por exemplo e mais recentemente "Como realizar cálculos com horas no SQL Server").

No último exemplo de cálculos horários foi exposta uma situação em que era necessário informar a quantidade de horas e minutos gasta em um determinado processo. Conforme apresentado, os tipos SMALLDATETIME (ou DATETIME) e tipos inteiros eram adequados enquanto o tipo de dados TIME era inadequado. Que outros tipos poderiam ser utilizados fora os apresentados ? O CHAR até poderia ser utilizado se o objetivo fosse apenas apresentar os dados, mas considerando que cálculos serão realizados, o tipo de dados CHAR não é uma escolha factível. Os tipos nativos ficam portanto esgotados restando portanto tipos próprios quer sejam User Defined Types (CLR) ou User Defined Data Types (TSQL). A utilização de User Defined Data Types não representa nenhuma alternativa visto que os tipos base (System Data Types) não oferecem nenhuma outra possibilidade que já não tenha sido apresentada (SMALLDATETIME, DATETIME, TINYINT e TIME). Resta então a implementação de tipos utilizando o CLR (User Defined Types).

Através do CLR, pode-se implementar um tipo de dados próprio para viabilizar as operações desejadas. O código abaixo cria esse tipo de dados que chamarei de CargaHoraria.

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

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
public struct CargaHoraria : INullable
{
    private SqlInt32 vHoras;
    private SqlInt32 vMinutos;
    private bool Nulo;

    public override string ToString()
    {
        // Retorna o horário formatado "hh:mm"
                string chr = Horas.ToString() + ":" + Minutos.ToString();

        if (Horas < 10)
        {
            chr = "0" + chr;
        }

        if (Minutos < 10)
        {
            chr = chr.Replace(":", ":0");
        }

        return chr;

    }

    // Implementa a propriedade de verificação de valor nulo
    public bool IsNull
    {
        get
        {
            return Nulo;
        }
    }

    // Retorna a quantidade de horas
    public SqlInt32 Horas
    {
        get
        {
            return vHoras;
        }
        set
        {
            vHoras = value;
        }
    }

    // Retorna a quantidade de minutos
    public SqlInt32 Minutos
    {
        get
        {
            return vMinutos;
        }
        set
        {
            //Se houver mais de sessenta minutos um erro é gerado
            if (value > 60)
            {
                throw new ArgumentException("A quantidade máxima de minutos é de 60");
            }
            else
            {
                vMinutos = value;
            }
        }
    }

    // Tratamento para uma atribuição de valor nulo
    public static CargaHoraria Null
    {
        get
        {
            CargaHoraria ch = new CargaHoraria();
            ch.Nulo = true;
            return ch;
        }
    }

    // Inicializa a classe
    public static CargaHoraria Parse(SqlString Valor)
    {
        // Se o valor informado for nulo retorna nulo
        if (Valor.IsNull)
            return Null;

        // Instancia uma nova classe (o formato hh:mm é esperado)
        try
        {
            CargaHoraria ch = new CargaHoraria();

            int Result;

            // Verifica se o parâmetro é numérico
            if (Int32.TryParse(Valor.ToString(), out Result))
            {
                ch.Horas = Result / 60;
                ch.Minutos = Result % 60;
            }

            else
            {
                // Localiza o caractér ":"
                int Pos = Valor.ToString().IndexOf(":");

                // Captura o total de horas
                SqlInt32 Horas = SqlInt32.Parse(Valor.ToString().Substring(0, Pos));

                // Captura o total de minutos
                SqlInt32 Minutos = SqlInt32 .Parse(
                    Valor.ToString().Substring(Pos + 1, (Valor.ToString().Length – Pos – 1)));

                ch.Horas = Horas;
                ch.Minutos = Minutos;
            }
            return ch;
        }

        catch
        {
            throw new ArgumentException("A inicialização do tipo carga horária está incorreta");
        }
    }

    // Método para retorno do total em minutos
    public SqlInt32 TotalMinutos()
    {
        return (Horas * 60) + Minutos;
    }
}

Esse tipo de dados possui duas propriedades inteiras (Horas e Minutos). Após instanciá-lo é possível também utilizar dois métodos. O primeiro retorna o dados formatado e o segundos mostra o total de minutos computados. É possível também inicializar o tipo com o formato hh:mm ou com uma quantidade fixa de minutos. Antes que ele possa ser utilizado é necessário compilá-lo. 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 CalculaCargaHoraria.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\CalculaCargaHoraria.cs (supondo que o arquivo esteja em G:\CLR\)
  • Localizar o arquivo CalculaSomaHoras.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 do novo tipo de dados.

— Carrega o Assembly para o SQL Server
CREATE ASSEMBLY CH FROM ‘G:\CLR\CalculaCargaHoraria.dll’

— Cria um novo tipo CargaHoraria a partir do Assembly
CREATE TYPE dbo.CargaHoraria
EXTERNAL NAME CH.CargaHoraria

Agora que o tipo de dados está devidamente registrado, é possível utilizá-lo. O script abaixo mostra alguma de suas potencialidades (dessea vez a quantidade de horas não está restrita a 24).

— Declara uma variável do tipo "CargaHoraria"
DECLARE @CH dbo.CargaHoraria

— Inicializa a variável com um valor no formato hh:mm
SET @CH = ’50:30′

— Mostra as propriedades e os métodos
SELECT @CH.Horas As Horas, @CH.Minutos As Minutos,
    @CH.TotalMinutos() As TotalMinutos, @CH.ToString() As Horario

— Inicializa a variável com um valor em quantidade de minutos
SET @CH = ‘635’

— Mostra as propriedades e os métodos
SELECT @CH.Horas As Horas, @CH.Minutos As Minutos,
    @CH.TotalMinutos() As TotalMinutos, @CH.ToString() As Horario

Após o teste, é possível visualizar que o tipo de dados está totalmente funcional e pode ser utilizado não somente na declaração de variáveis, mas como parâmetros de Stored Procedures, Functions e colunas de tabelas. A última possibilidade é a que mais interessa para esse artigo. Abaixo o script de criação de tabelas e devidos registros com esse novo tipo de dados.

CREATE TABLE Deptos (IDDepto INT, NomeDepto VARCHAR(50))
CREATE TABLE Processos (IDProcesso INT, IDDepto INT, NomeProcesso VARCHAR(200))
CREATE TABLE Medicoes (IDMedicao INT, IDProcesso INT, Duracao dbo.CargaHoraria)

INSERT INTO Deptos VALUES (1,‘RH’)
INSERT INTO Deptos VALUES (2,‘Financeiro’)

INSERT INTO Processos VALUES (1,1,‘Avaliar Currículos’)
INSERT INTO Processos VALUES (2,1,‘Efetuar Cálculos Trabalhistas’)
INSERT INTO Processos VALUES (3,1,‘Realizar Provas Práticas’)
INSERT INTO Processos VALUES (4,2,‘Conciliar Pagamentos Bancários’)
INSERT INTO Processos VALUES (5,2,‘Conferir Ordens de Pagamento’)

INSERT INTO Medicoes VALUES (01,1,’04:00′)
INSERT INTO Medicoes VALUES (02,1,’05:30′)
INSERT INTO Medicoes VALUES (03,1,’03:45′)
INSERT INTO Medicoes VALUES (04,2,’15:00′)
INSERT INTO Medicoes VALUES (05,2,’13:35′)
INSERT INTO Medicoes VALUES (06,3,’08:00′)
INSERT INTO Medicoes VALUES (07,4,’40:00′)
INSERT INTO Medicoes VALUES (08,4,’36:00′)
INSERT INTO Medicoes VALUES (09,4,’37:25′)
INSERT INTO Medicoes VALUES (10,4,’38:15′)
INSERT INTO Medicoes VALUES (11,5,’12:10′)
INSERT INTO Medicoes VALUES (12,5,’13:20′)

Após inserir as linhas no novo tipo de dados é perfeitamente possível utilizar alguns dos métodos propostos.

SELECT
    NomeDepto, NomeProcesso, Duracao,
    Duracao.ToString() As ToString,
    Duracao.TotalMinutos() As TotalMinutos
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso

O resultado é exposto na tabela abaixo (apenas parte dos registros)

NomeDepto

NomeProcesso

Duracao

ToString

TotalMinutos

RH

Avaliar Currículos

0x0180000004018000000000

04:00

240

RH

Efetuar Cálculos Trabalhistas

0x018000000F018000000000

15:00

900

RH

Realizar Provas Práticas

0x0180000008018000000000

08:00

480

Financeiro

Conciliar Pagamentos Bancários

0x0180000028018000000000

40:00

2400

Financeiro

Conferir Ordens de Pagamento

0x018000000C018000000A00

12:10

730

Os curiosos para saber o que tem na coluna podem ver que se trata de um binário difícil de decifrar, mas que se utilizarmos os métodos podemos extrair os dados que realmente interessam. O método "ToString" mostra exatamente o valor no formato hh:mm enquanto o método "TotalMinutos" mostra a quantidade de minutos presente em cada registro. De posse desses valores é perfeitamente possível efetuar somatórios.

SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso

O resultado é exposto na tabela abaixo:

NomeDepto

NomeProcesso

Quantidade

DuracaoMedia

Financeiro

Conciliar Pagamentos Bancários

4

2275

Financeiro

Conferir Ordens de Pagamento

2

765

RH

Avaliar Currículos

3

265

RH

Efetuar Cálculos Trabalhistas

2

857

RH

Realizar Provas Práticas

1

480

É possível converter a coluna "DuracaoMedia" para o formato hh:mm através das fórmulas anteriores. Ex:

;WITH Duracoes (NomeDepto, NomeProcesso, Quantidade, Media)
As (
SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

SELECT NomeDepto, NomeProcesso, Quantidade,
    RIGHT(‘0’ + CAST((Media / 60) As VARCHAR(2)),2) + ‘:’ +
    RIGHT(‘0’ + CAST((Media % 60) As VARCHAR(2)),2) As DuracaoMedia
FROM Duracoes

Essa fórmula foi usada nos artigos anteriores, mas ela possui algumas limitações. Ele funcionará bem para formatos como hh:mm mas basta que algum processo supere a quantidade de 99 horas e o formato hh:mm irá falhar. É possível rearranjar a formatação, mas como há um tipo em CLR que pode ser inicializado com uma quantidade de minutos, ele também pode ser utilizado.

;WITH Duracoes (NomeDepto, NomeProcesso, Quantidade, Media, Total)
As (
SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia,
    SUM(Duracao.TotalMinutos()) As DuracaoTotal
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso),

Resultado (Depto, Processo, Quantidade, DMedia, DTotal)
As (
SELECT
    NomeDepto, NomeProcesso, Quantidade,
    CONVERT(dbo.CargaHoraria,CAST(Media As VARCHAR(7))),
    CONVERT(dbo.CargaHoraria,CAST(Total As VARCHAR(7)))
FROM Duracoes)

SELECT
    Depto, Processo, Quantidade,
    DMedia.ToString() As DuracaoMedia,
    DTotal.ToString() As DuracaoTotal
FROM Resultado

O resultado final é exposto na tabela abaixo:

NomeDepto

NomeProcesso

Quantidade

DuracaoMedia

DuracaoTotal

Financeiro

Conciliar Pagamentos Bancários

4

37:55

151:40

Financeiro

Conferir Ordens de Pagamento

2

12:45

25:30

RH

Avaliar Currículos

3

04:25

13:15

RH

Efetuar Cálculos Trabalhistas

2

14:17

28:35

RH

Realizar Provas Práticas

1

08:00

08:00

Se a fórmula que utilizou o RIGHT fosse aplicada, a situação da coluna "Duração Total" do primeiro registro não será apresentada corretamente. Com as conversões para o tipo "CargaHoraria" é possível repassar como parâmetro uma quantidade de minutos através de uma string. Poderia ser utilizado o tipo INT diretamente, mas provocar uma sobrecarga do método no CLR é "trabalho demais". Abaixo a consulta utilizando o RIGHT.

;WITH Duracoes (NomeDepto, NomeProcesso, Quantidade, Media, Total)
As (
SELECT
    NomeDepto, NomeProcesso, COUNT(Duracao) As Quantidade,
    AVG(Duracao.TotalMinutos()) As DuracaoMedia,
    SUM(Duracao.TotalMinutos()) As DuracaoTotal
FROM
    Deptos
    INNER JOIN Processos ON Deptos.IDDepto = Processos.IDDepto
    INNER JOIN Medicoes ON Processos.IDProcesso = Medicoes.IDProcesso
GROUP BY
    NomeDepto, NomeProcesso)

SELECT NomeDepto, NomeProcesso, Quantidade,

    CASE WHEN (Media / 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Media / 60) As VARCHAR(7)),7)
    ELSE
        CAST((Media / 60) As VARCHAR(7))
    END + ‘:’ +

    CASE WHEN (Media % 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Media % 60) As VARCHAR(7)),7)
    ELSE
        CAST((Media % 60) As VARCHAR(7))
    END As DuracaoMedia,

    CASE WHEN (Total / 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Total / 60) As VARCHAR(7)),7)
    ELSE
        CAST((Total / 60) As VARCHAR(7))
    END + ‘:’ +

    CASE WHEN (Total % 60) <= 10 THEN
        RIGHT(‘0’ + CAST((Total % 60) As VARCHAR(7)),7)
    ELSE
        CAST((Total % 60) As VARCHAR(7))
    END As DuracaoTotal

FROM Duracoes

Após essa última consulta é possível ver que o uso do CLR também é muito bem vindo em situações do dia-a-dia. Normalmente esse caso se beneficiaria melhor das construções e tipos nativos do SQL Server, mas o intuito é demonstrar que o CLR também pode ser utilizado.

[ ]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