Seleção de datas não armazenadas

Bom Dia Pessoal,

Após praticamente virar uma noite por conta de alguns incidentes envolvendo o SQL resolvi blogar um pouco enquanto aguardo o aceite de uma das equipes envolvidas no incidente. Dando continuidade aos dois últimos artigos, tratarei sobre mais um dos problemas relatados no meu último Webcast, o problema das datas não armazenadas.

As datas tem um papel importante em diversos relatórios gerenciais (não é a toa que aplicações OLAP tem uma dimensão própria para isso). A todo instante utiliza-se as datas para formar tendências, acompanhar o desempenho do negócio ao longo do tempo, etc. Essas datas normalmente representam uma coluna de alguma tabela que será o alvo da consulta para essas tendências e análises de desempenho.

Na maioria das situações é esperado verificar um intervalo contíguo de datas, ou seja, todo dia é vendido algum produto, todo mês há algum lançamento contábil, etc mas pode acontecer do intervalo não ser contíguo, ou seja, aparecer a presença de gaps. Suponha por exemplo que você tenha ocorrência de vendas do dia 1, do dia 2, do dia 5 e do dia 6. Os dias 3 e 4 não estão presentes, pois, nesses dias não houve nenhuma ocorrência de venda. Quando a consulta for realizada, você poderá fazer diversas análises com base nesses dias, mas e se for necesário de fato mostrar que os dias 3 e 4 não possuem vendas ? Se não houve vendas então não há registros desses dias na base e não haveria como fazer com que eles aparecessem "do nada" a menos que se soubesse previamente os dias que não tiveram vendas (o que na maioria das vezes não é factível para elaborar uma consulta). Esse é o problema que chamo de "datas não armazenadas".

O primeiro passo para demonstrar como solucionar esse problema é criando condições para reproduzí-lo. O script abaixo cria uma tabela de vendas e popula alguns registros de vendas. Podemos notar que as vendas se iniciam em abril de 2007 e vão até outubro de 2008. Durante esse período existem vários gaps como por exemplo maio de 2007 e setembro de 2008 que não possuem vendas efetuadas.

CREATE TABLE tblVendasConsolidadas (
    Data SMALLDATETIME,
    TotalVendido NUMERIC(10,2))

INSERT INTO tblVendasConsolidadas VALUES (‘20070401’,2000)
INSERT INTO tblVendasConsolidadas VALUES (‘20070601’,2500)
INSERT INTO tblVendasConsolidadas VALUES (‘20070701’,3000)
INSERT INTO tblVendasConsolidadas VALUES (‘20070801’,3200)
INSERT INTO tblVendasConsolidadas VALUES (‘20071101’,4800)
INSERT INTO tblVendasConsolidadas VALUES (‘20071201’,5000)
INSERT INTO tblVendasConsolidadas VALUES (‘20080101’,3100)
INSERT INTO tblVendasConsolidadas VALUES (‘20080201’,2500)
INSERT INTO tblVendasConsolidadas VALUES (‘20080301’,1300)
INSERT INTO tblVendasConsolidadas VALUES (‘20080401’,1000)
INSERT INTO tblVendasConsolidadas VALUES (‘20080601’,4000)
INSERT INTO tblVendasConsolidadas VALUES (‘20080701’,5000)
INSERT INTO tblVendasConsolidadas VALUES (‘20080801’,5500)
INSERT INTO tblVendasConsolidadas VALUES (‘20081001’,4500)

Poderíamos visualmente perceber todos os meses faltantes e posteriormente realizar um UNION ALL com os meses faltantes mas essa abordagem não é muito prática, pois, teríamos que saber previamente os meses bem codificar de forma fixa os meses faltantes. Essa solução pode funcionar para uma eventual pesquisa feita diretamente no banco de dados, mas é nada prática para ficar codificada em uma aplicação.

Se a idéia é exibir todas as datas durante um intervalo, teríamos um intervalo contíguo sem gaps. A partir da primeira data podemos construir esse intervalo adicionando um mês à primeira data e obtendo o próximo mês. Esse processo seria repetido seguidas vezes até alcançarmos o período final. O script abaixo demonstra  bem essa linha de raciocínio.

DECLARE @MenorData SMALLDATETIME, @MaiorData SMALLDATETIME

— Captura a menor e a maior data da tabela de vendas
SELECT @MenorData = MIN(Data), @MaiorData = MAX(Data)
FROM tblVendasConsolidadas

WHILE @MenorData <= @MaiorData
BEGIN
    SELECT @MenorData
    — Adiciona um mês à menor data
    SET @MenorData = DATEADD(M,1,@MenorData)
END

Com a consulta acima, podemos perceber um intervalo de 19 datas compreendendo todos os meses existentes entre a primeira venda da tabela (04/2007) e a última (10/2008). Poderíamos armazenar esses valores em uma tabela a parte e depois fazer um JOIN com a tabela de vendas, mas o script acima é procedural e não conseguiríamos encapsulá-lo em uma instrução SELECT. O fato é que conhecida a primeira data, se a adicionarmos a uma seqüência númerica (no nosso caso de 1 a 19) poderemos obter um intervalo de datas contíguas. No artigo anterior (criando uma tabela com uma seqüência de números) demonstrei como obter uma seqüência de números. Vou utilizar a função criada nesse artigo e aplicar a lógica do WHILE. Se você não possui a função que estou utilizando, acesse o artigo que referenciei e crie-a.

DECLARE @MenorData SMALLDATETIME, @MaiorData SMALLDATETIME

— Captura a menor data da tabela de vendas
SELECT @MenorData = MIN(Data) FROM tblVendasConsolidadas

— Monta uma tabela de datas em seqüência
SELECT @MenorData As PrimeiraData, Num,
DATEADD(M,Num-1,@MenorData) As DataIntervalo
FROM dbo.FNRetNum(19)

Na primeira coluna, temos um valor fixo que consiste na menor data existente na tabela de vendas (04/2007). Na segunda coluna temos uma seqüência de números de 1 a 19 (foi repassado 19 como parâmetro para a função). Na terceira coluna temos uma data que consiste na data da primeira coluna, adicionando uma quantidade de meses igual a da segunda coluna e retirando um unidade. Podemos perceber que a terceira coluna é exatamente igual ao intervalo produzido no WHILE mas de forma declarativa e não procedural. Isso significa entre outras coisas que poderemos reaproveitá-la em cláusulas SELECT.

A consulta anterior produziu as datas de que precisamos, ou seja, todas os meses entre abril de 2007 e outubro de 2008, mas tivemos que descobrir que esse intervalo representava 19 meses e informar o número 19 como parâmetro da função. Se 19 for a diferença de meses, basta então substituir esse valor pela diferença em meses e executar a função. Ex:

DECLARE @MenorData SMALLDATETIME, @MaiorData SMALLDATETIME

— Captura a menor e a maior data da tabela de vendas
SELECT @MenorData = MIN(Data), @MaiorData = MAX(Data)
FROM tblVendasConsolidadas

— Monta uma tabela de datas em seqüência
SELECT @MenorData As PrimeiraData, Num,
DATEADD(M,Num-1,@MenorData) As DataIntervalo
FROM dbo.FNRetNum(DATEDIFF(M,@MenorData,@MaiorData)+1)

No script passei como parâmetro da função a diferença em meses de abril de 2007 a outubro de 2008 mas adicionei uma unidade. A diferença entre os meses é igual a 18, mas desejamos exibir todos os meses, por isso o acréscimo de uma unidade. A consulta acima produziu todas as datas de que necessitamos. Agora basta apenas efetuar um JOIN desse resultado com a tabela de vendas.

— Cria variáveis para capturar a menor e a maior data
DECLARE @MenorData SMALLDATETIME, @MaiorData SMALLDATETIME

SELECT @MenorData = MIN(Data), @MaiorData = MAX(Data)
FROM tblVendasConsolidadas

— Faz um JOIN do intervalo de datas com as datas vigentes
— O LEFT OUTER para considerar todas as datas do intervalo

;WITH Datas (DataRef) AS (
SELECT DATEADD(M,Num-1,@MenorData)
FROM dbo.FNRetNum(DATEDIFF(M,@MenorData,@MaiorData)+1))

SELECT DataRef, ISNULL(TotalVendido,0) AS ValorVendido
FROM Datas AS D
LEFT OUTER JOIN tblVendasConsolidadas AS V ON D.DataRef = V.Data

Dessa forma conseguimos produzir todas as vendas entre o intervalo de 04/2007 a 10/2008 e para os gaps, o LEFT OUTER JOIN em conjunto com o ISNULL irá preencher o valor zero sinalizando que nenhuma venda foi efetuada. Se fosse necessário efetuar um filtro (digamos entre 06/2007 e 08/2008) a consulta ficaria intacta, bastando filtrar na cláusula WHERE. Para otimizar o código, o filtro correto deve ficar na atribuição de variáveis e não na consulta final. Ex:

— Cria variáveis para capturar a menor e a maior data
— O intervalo é entre 06/2007 e 08/2008

DECLARE @MenorData SMALLDATETIME, @MaiorData SMALLDATETIME

SELECT @MenorData = MIN(Data), @MaiorData = MAX(Data)
FROM tblVendasConsolidadas
WHERE Data >= ‘20070601’ AND Data < ‘20080901’

— Faz um JOIN do intervalo de datas com as datas vigentes
— O LEFT OUTER para considerar todas as datas do intervalo

;WITH Datas (DataRef) AS (
SELECT DATEADD(M,Num-1,@MenorData)
FROM dbo.FNRetNum(DATEDIFF(M,@MenorData,@MaiorData)+1))

SELECT DataRef, ISNULL(TotalVendido,0) AS ValorVendido
FROM Datas AS D
LEFT OUTER JOIN tblVendasConsolidadas AS V ON D.DataRef = V.Data

Dessa forma o intervalo já é montado corretamente, ao invés de se montá-lo com todas as datas e posteriormente descartar as datas que não relevantes. Essa é uma aplicação bem prática da função que descrevi no artigo anterior para um problema bem comum. Existem outras formas de resolver sem utilizar a função como cursores, tabelas temporárias, etc mas não estou certo se irão ser mais performáticas e mais simples do que essa. Para uma outra solução e consultas mais aprofundadas sobre esse problema não deixe de consultar o link abaixo:

Find Missing Date Ranges in SQL
http://www.simple-talk.com/sql/t-sql-programming/find-missing-date-ranges-in-sql/

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