Criando filtros customizados para consultas no gerente de consultas

Olá pessoal, essa semana passei por uma situação em que era necessário criar uma consulta com critérios de seleção customizados, e ao chegar no resultado final, acreditei que seria interessante compartilhar este artificio.

Bom vamos ao nosso cenário fictício:

Supomos que temos a necessidade de criar filtros para a seguinte consulta(chamarei de Consulta Situação Nota Fiscal):

SELECT
   Chave,
   [Número do Documento],
   [Código do PN],
   Status,
   [Data de Lançamento],
   [Data de vencimento],
   [Data do Documento]
FROM
       (SELECT
             DocEntry AS 'Chave',
  DocNum AS 'Número do Documento',
  CardCode AS 'Código do PN',
             CASE
             WHEN CANCELED = 'Y' THEN
                    'Cancelada'
             WHEN DocStatus = 'C' THEN
                    'Fechada'
             WHEN DocStatus = 'O' THEN
                    'Aberta'
             END AS Status,
  DocDate AS 'Data de Lançamento',
  DocDueDate AS 'Data de vencimento',
  TaxDate AS 'Data do Documento'
             FROM OINV) AS
       T0

Para esta consulta criaremos 4 filtros que são:

1. Tipo de data que deve ser filtrado: Data de pagamento, Data de vencimento ou data de emissão.

2. Data Inicial

3. Data Final

4. Status do documento: Aberto, Fechado, Cancelado e Todos.

No entanto, de maneira tradicional, não é possível colocar nos critérios de seleção, o rotulo que você deseja em cada filtro. Também não é possível ter um combo com opções customizadas. Essa situação pode ser resolvida com a criação de uma UDT(Tabela de usuário) do tipo “sem objeto”.

Vamos a resolução:

Criarei uma tabela chamada Filtros.

Com a tabela criada, vamos a criação dos campos:

Os filtros Data Inicial e Data Final terão apenas uma nova descrição. Então criarei dois campos um com o titulo DtIni e com a descrição “Data Inicial”(estará na imagem abaixo) e outro com o titulo DtFim e com a descrição “Data Final”, ambos do tipo Data/Hora, com estrutura do tipo Data.

Agora vamos aos filtros onde é necessário um combo. Criaremos da mesma forma como apresentada acima, porém esses dois filtros serão do tipo alfanúmerico. O artificio para esses filtros é a adição de valores válidos. Dessa forma poderemos usar eles no combo da tela de critérios de seleção.

Na criação dos valores válidos, usarei no campo valor e no campo descrição a mesma informação.

Agora com nossas opções de filtros criadas, iremos chama-lá a partir do gerador de consulta da seguinte forma:

SELECT * FROM [@FILTROS] T0 WHERE T0.[U_TipoData] = '[%0]' AND
T0.[U_DtIni] >= '[%1]' AND T0.[U_DtFim] <= '[%2]' AND T0.[U_Status] = '[%3]'

Na instrução acima, estou apenas chamando os filtros , não estou aplicando eles na minha consulta ainda. O comando acima pode ser entendido como:

%0 = Apareça em tela um filtro relacionado ao campo TipoData da tabela @Filtros com o texto “Igual a”.

%1 = Apareça em tela um filtro relacionado ao campo DtIni da tabela @Filtros com o texto “Maior ou igual”.

%2 = Apareça em tela um filtro relacionado ao campo DtFim da tabela @Filtros com o texto “Menor ou igual”.

%3 = Apareça em tela um filtro relacionado ao campo Status da tabela @Filtros com o texto “Igual a”.

A linha acima será executada como consulta, mas não retornará nada, pois a tabela está vazia. Mesmo que retornasse algo, para esse nosso cenário, o retorno não importa.

Agora vamos ler esses filtros para variáveis, para que possamos usar nas nossas consultas.

DECLARE @TipoData AS NVARCHAR(20)
DECLARE @DtIni AS DATETIME
DECLARE @DtFim AS DATETIME
DECLARE @Status AS NVARCHAR(10)
/* SELECT FROM [@FILTROS] T0 WHERE */ SET @TipoData /* T0.[U_TipoData] = */ = '[%0]' /* AND */
SET @DtIni /* T0.[U_DtIni] >= */ = '[%1]' /* AND */ SET @DtFim /* T0.[U_DtFim] <= */ = '[%2]' /*
AND */ SET @Status /* T0.[U_Status] = */ = '[%3]'

Com esta alteração estamos fazendo a mesma coisa descrita acima, porém agora, estamos também pegando o valor obtido no filtro e colocando esse valor nas nossas variáveis. Nesse momento basta apenas alterar nossa consulta para utilizar as variáveis obtida. Dessa forma nosso resultado final ficará:

DECLARE @TipoData AS NVARCHAR(20)
DECLARE @DtIni AS DATETIME
DECLARE @DtFim AS DATETIME
DECLARE @Status AS NVARCHAR(10)
/* SELECT FROM [@FILTROS] T0 WHERE */ SET @TipoData /* T0.[U_TipoData] = */ = '[%0]' /* AND */
SET @DtIni /* T0.[U_DtIni] >= */ = '[%1]' /* AND */ SET @DtFim /* T0.[U_DtFim] <= */ = '[%2]' /*
AND */ SET @Status /* T0.[U_Status] = */ = '[%3]'
SELECT
   Chave,
   [Número do Documento],
   [Código do PN],
   Status,
   [Data de Lançamento],
   [Data de vencimento],
   [Data do Documento]
FROM
  (SELECT
  DocEntry AS 'Chave',
  DocNum AS 'Número do Documento',
  CardCode AS 'Código do PN',
  CASE
  WHEN CANCELED = 'Y' THEN
  'Cancelado'
  WHEN DocStatus = 'C' THEN
  'Fechado'
  WHEN DocStatus = 'O' THEN
  'Aberto'
  END AS Status,
  DocDate AS 'Data de Lançamento',
  DocDueDate AS 'Data de vencimento',
  TaxDate AS 'Data do Documento'
  FROM OINV) AS
  T0
WHERE
  @DtIni <=
  CASE
  WHEN @TipoData = 'Data de Lançamento' THEN
  T0.[Data de Lançamento]
  WHEN @TipoData = 'Data de vencimento' THEN
  T0.[Data de vencimento]
  WHEN @TipoData = 'Data do Documento' THEN
  T0.[Data do Documento]
  END
  AND
  @DtFim >=
  CASE
  WHEN @TipoData = 'Data de Lançamento' THEN
  T0.[Data de Lançamento]
  WHEN @TipoData = 'Data de vencimento' THEN
  T0.[Data de vencimento]
  WHEN @TipoData = 'Data do Documento' THEN
  T0.[Data do Documento]
  END
  AND
  CASE
  WHEN @Status = 'Todos' THEN
  ''
  ELSE
  T0.Status
  END
  =
  CASE
  WHEN @Status = 'Todos' THEN
  ''
  ELSE
  @Status
  END

Ao executar nossa consulta, teremos a seguinte situação:

Espero que possa ser útil.

Dúvidas e sugestões são bem vindas.

Att,

Diego Lother