Crie relatórios do SQL Server usando funções de data e hora

Crie relatórios do SQL Server usando funções de data e hora

SQL Server Reports - create reporting intervals - the data model we'll use in the article
cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br


Nos dois artigos anteriores desta série, discutimos como criar um relatório e como usar funções internas de data e hora. Hoje vamos nos juntar
    esses dois juntos e analisamos algumas consultas interessantes que poderíamos usar ao criar relatórios mais complexos do SQL Server
    onde as funções de data e hora são usadas para criar categorias de relatório.

Modelo de dados e algumas considerações gerais

Usaremos o mesmo modelo de dados que estamos usando ao longo desta série. Como estaremos focados principalmente em como gerar
    categorias de relatório de data (hora), usaremos dados de apenas uma tabela e esse é o ligar
mesa.

Relatórios do SQL Server - criar intervalos de relatório - o modelo de dados que usaremos no artigo

A idéia principal é criar várias consultas que possam ser usadas como estão neste artigo ou facilmente ajustadas para atender
    os requisitos definidos para o relatório específico do SQL Server. Usaremos as instruções e funções que utilizamos anteriormente
    mencionado, mas também apresentaremos alguns novos conceitos neste artigo. Estes novos conceitos serão cobertos apenas em
    a quantidade necessária para entender as instruções SQL deste artigo, e falaremos mais sobre elas posteriormente no
    Series.

Criando um período para um relatório do SQL Server (liste todas as datas em um determinado mês)

Um dos requisitos de relatório mais comuns é gerar um relatório para o período – incluindo todas as datas em
    esse intervalo. Por exemplo. vamos supor que queremos listar todas as chamadas no mês especificado. Queremos ter cada data naquele mês
    juntamente com o número de chamadas iniciadas nessa data.

Você poderia supor que podemos facilmente receber chamadas do ligar tabela e agrupe-os por data. E isso é
    ESTÁ BEM. Mas o problema é que podemos não ter chamadas para algumas datas e, para essas datas, não teríamos uma briga na final
    resultado. E queremos ter essa linha presente com o valor 0 (não apenas para que o relatório pareça melhor dessa maneira, mas
    também sabemos que a linha não está faltando como resultado de um erro etc.).

Para isso, precisamos criar uma lista de todas as datas e, em seguida, ingressar nessa lista na consulta com os relatórios
    dados (aquele que seleciona dados do ligar mesa).

Poderíamos criar uma lista de datas usando o conjunto de comandos, conforme apresentado abaixo. Vamos dar uma olhada nesses comandos como
    bem como a sua saída.

Exemplo de SQL - todas as datas em maio de 2020

Essas são algumas coisas importantes que eu gostaria de salientar relacionadas ao código apresentado acima. Vou começar do
    final, e esse é o resultado final. Duas consultas retornaram dois resultados:

Leia Também  Pare de usar este anti-padrão UPSERT

  • A primeira consulta retornou 3 valores cur_date (data especificada), first_date (a primeira data em um mês igual a um mês
            da data especificada) e last_date (a última data em um mês igual a um mês da data especificada). Esta consulta foi
            usado apenas para mostrar que recebemos a primeira e a última data do mês a partir da data original. O resultado foi
            usado para a segunda consulta

  • A segunda consulta retornou todas as datas no mês especificado. Observe que, além dessas datas, não temos nenhuma
            outros dados

Agora vamos explicar o que fizemos para conseguir isso. Ainda assim, antes de fazer isso, vamos mencionar alguns novos conceitos que temos
    usava:

  • DECLARE @date DATE; declara uma variável do ENCONTRO tipo. Mais tarde, atribuiremos valor a essa variável – SET @date = ‘2020/05/12’;e use-o no código, quando necessário. Variáveis ​​são usadas (em bancos de dados, mas também em programação)
            como um local para armazenar os valores necessários mais tarde. No SQL Server (e de modo geral), você pode definir variáveis ​​de qualquer tipo de dados disponível usado para colunas
  • DECLARE @dates TABLE (date DATE); Também é possível definir o MESA variável de tipo. Essa variável deve ser
            usado para armazenar uma tabela completa e pode ser usado como uma tabela padrão – você pode inserir novos dados ou selecionar nessa variável (é o que faremos no código) ou executar outras operações relacionadas à tabela
  • Nós usamos o ENQUANTO ciclo. Na programação, loops são usados ​​para executar um determinado código tantas vezes quanto o loop
            condição é verdadeira. O loop WHILE deve ser executado enquanto a condição permanece – que pode ser 0 vezes, apenas uma vez, várias vezes (a situação mais comum) ou até um número infinito de vezes (geralmente é o resultado de um erro)

E foi isso que fizemos para alcançar o resultado:

  • Primeiro, definimos uma data desejada -> SET @date = ‘2020/05/12’. Esta é a data do mês em que queremos ter todas as datas em uma lista

  • Com essas duas declarações, definimos a primeira e a última data no mês especificado -> SET @start_date = DATEFROMPARTS (ANO (@data), MÊS (@data), ’01’); SET @ end_date = EOMONTH (@date); Para funções de data e hora, consulte o artigo Aprenda SQL: funções de data e hora do SQL Server

  • Usamos o loop WHILE para ir da data de início até a data de término e inserimos cada data 1 por 1 na variável da tabela (INSERT INTO @dates (date) VALUES (@loop_date);) Observe que aumentamos a data em 1 em cada etapa / passo do loop (SET @loop_date = DATEADD (DAY, 1, @loop_date);)

  • No final, selecionamos da variável da tabela para obter a lista completa

O resultado de tudo isso é que, para qualquer data, podemos listar todas as datas naquele mês, do primeiro ao último. Mais tarde
    poderíamos associar essas datas aos dados reais do relatório e incluir no mesmo datas sem relatórios relacionados.

Além disso, você pode facilmente ajustar esse código (alterar a maneira como o @start_date e o @end_date são calculados) e
    listar todas as datas, por exemplo na semana indicada; começando 20 dias antes de uma determinada data e terminando 20 dias depois; no dado
    ano etc.

JUNTE-SE a um período e aos dados do relatório

Na etapa anterior, criamos uma lista de datas dos relatórios que queremos apresentar no relatório do SQL Server.
    Observe que, ao criar esta lista, não consultamos nenhuma tabela do banco de dados. Agora, ingressaremos nesta lista no
    dados do nosso banco de dados. A primeira coisa a fazer é verificar o que está no ligar mesa.

Todas as chamadas na tabela do banco de dados

Podemos concluir que temos 10 chamadas e todas elas foram feitas em 11/01 2020. Vamos usar o código do anterior
    seção deste artigo e combine-a com a tabela de chamadas para criar um relatório real.

Exemplo de SQL - todas as chamadas em janeiro

Observe duas coisas:

  • Temos a mesma lista de datas no 1st coluna, mas também várias chamadas realizadas diariamente em
            o 2nd coluna (todas são 0, exceto 10 chamadas em 11-01 2020)

  • Na última consulta (SELECT), usamos LEFT JOIN para ter todos
            as datas presentes no relatório final, COUNT (call.id) contar ids
            e não linhas (a contagem de linhas retornaria 1 para cada dia em que tivermos 0), usou um nome alternativo para a tabela
            variável – FROM @dates de hora de início convertida / convertida para ENCONTRO (é originalmente DATETIME) –
    CAST (call.start_time AS DATE)

Criando um intervalo de data e hora em minutos (como uma categoria de relatório)

Até agora, usamos apenas datas como categorias de relatórios. Tudo bem se quisermos que nosso relatório do SQL Server seja granulado em um
    nível diário. Mas o que fazer se quisermos criar um relatório com diferentes intervalos de tempo (horas, minutos, segundos,
    etc.)? Vamos primeiro dar uma olhada no código abaixo.

Relatórios do SQL Server - intervalos de 10 minutos

A saída deste relatório são intervalos de 10 minutos em 11-01 2020 e o número de chamadas iniciadas durante esses
    intervalos. Vamos ver como conseguimos isso.

A idéia do código acima é poder definir a hora de início e a hora de término do nosso relatório do SQL Server e
    intervalos. Definiremos intervalos por unidade de tempo e incremento. No nosso exemplo, as unidades de tempo são minutos e o
    o valor do incremento está definido como 10. Vamos analisar rapidamente nosso código:

  • DECLARE @start_time DATETIME; e DECLARE @end_time DATETIME; – são variáveis ​​nas quais armazenaremos / definiremos o início
            hora e hora de término do nosso relatório do SQL Server
  • DECLARAR @interval CHAR (3); – é usado para armazenar um código de 2 ou 3 letras que define a unidade de intervalo (minuto, segundo,
            hora etc.)
  • DECLARAR @increment INT; – é usado para armazenar o número de unidades por cada intervalo
  • Usamos novamente o ENQUANTO loop a loop da hora de início até a hora de término. Em cada etapa do loop, verificamos o
            intervalo definido e intervalo adicionado de acordo, inseriu uma linha na variável da tabela e aumentou o valor para
            a próxima passagem do loop

  • A última afirmação é a SELECT usando a variável de tabela e a tabela de chamadas, para contar o número de
            chama nos intervalos definidos na tabela @times

Vale a pena notar que você pode alterar facilmente a unidade de intervalo e incrementar e ajustar esse código de acordo com
    suas necessidades de relatórios.

Quando usar essas consultas (nos relatórios do SQL Server)

Já estive várias vezes em situações em que precisei criar relatórios por um determinado período. Caso você precise
    para fazer isso, você pode usar as consultas acima para criar categorias de data / data e hora para o seu relatório do SQL Server. Do
    Claro, você precisará ajustar um pouco as consultas e adicionar suas tabelas de relatórios (SELECTs), mas isso deve funcionar
    perfeitamente.

Índice

Emil Drkusic
Últimas mensagens de Emil Drkusic (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br