Ao analisar e relatar ocorrências de dados ao longo do tempo com uma agregação por parte da data, como semana, dia, hora, etc., é comum encontrar lacunas nos valores de data. Essas lacunas ocorrem quando não há dados disponíveis para uma determinada data ou hora. Nesses casos, é importante considerar esses valores ausentes e atribuir a eles um valor de 0 ocorrências.

Vamos considerar um exemplo para entender melhor. Suponha que temos uma tabela que registra ocorrências de erros por hora. A tabela pode ser assim:

Neste exemplo, podemos ver que faltam linhas para as horas 12:00 e 13:00. Se quisermos analisar os dados e incluir essas horas ausentes, precisamos preencher as lacunas de data com um marcador de data e atribuir 0 ocorrências a elas. A tabela modificada ficaria assim:

Para conseguir isso, podemos usar uma tabela de datas ou uma consulta de Expressão de Tabela Comum (CTE) para gerar uma Tabela de Datas virtual. Nos passos a seguir, demonstrarei uma opção para preencher a lacuna de data usando uma CTE:

Passo 1: Configurar a Expressão de Tabela Comum (CTE) de Data Virtual

Vamos criar uma Função de Tabela que usa uma CTE para gerar uma tabela de datas virtual. Essa função recebe parâmetros como a parte da data (ano, mês, semana, dia, hora ou minuto), data de início e data de término. Ela gera dinamicamente as datas com base na parte da data especificada e retorna uma tabela com as datas geradas.

CREATE FUNCTION [dbo].[Get_DateList_uft] (
         @PartofDate as VARCHAR(10), --ano, mês, semana, dia, hora ou minuto
         @StartDate AS SMALLDATETIME,
         @EndDate  AS SMALLDATETIME = GETDATE()
)
   RETURNS TABLE AS RETURN
   WITH 
   LIST0 (Numbers) AS (SELECT 0 UNION ALL SELECT 0),                     
   LIST1 (Numbers) AS (SELECT 0 FROM LIST0 A CROSS JOIN LIST0 B),        
   LIST2 (Numbers) AS (SELECT 0 FROM LIST1 AS A CROSS JOIN LIST1 AS B),  
   LIST3 (Numbers) AS (SELECT 0 FROM LIST2 AS A CROSS JOIN LIST2 AS B),  
   LIST4 (Numbers) AS (SELECT 0 FROM LIST3 AS A CROSS JOIN LIST3 AS B),  
   LIST5 (Numbers) AS (SELECT 0 FROM LIST4 AS A CROSS JOIN LIST4 AS B)   
 
   SELECT @StartDate as 'DateList' 
   UNION ALL
   SELECT 
   TOP (CASE @PartofDate
         WHEN 'DIA' THEN DATEDIFF(DAY,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'HORA' THEN DATEDIFF (HOUR,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'ANO' THEN DATEDIFF (YEAR,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'MÊS' THEN DATEDIFF (MONTH,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'SEMANA' THEN DATEDIFF (WEEK,@StartDate,COALESCE(@EndDate,GETDATE()))
         WHEN 'MINUTO' THEN DATEDIFF (MINUTE,@StartDate,COALESCE(@EndDate,GETDATE()))
      END)
      (CASE @PartofDate
         WHEN 'DIA' THEN DATEADD(DAY,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'HORA' THEN DATEADD(HOUR,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'ANO' THEN DATEADD(YEAR,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'MÊS' THEN DATEADD(MONTH,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'SEMANA' THEN DATEADD(WEEK,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
         WHEN 'MINUTO' THEN DATEADD(MINUTE,ROW_NUMBER() OVER (ORDER BY Numbers), @StartDate)
      END) AS DateList
   FROM LIST5

Passo 2: Configurar Dados de Exemplo

Para este exemplo, usaremos o log de erros do SQL como nossos dados de exemplo. Usaremos o procedimento armazenado do sistema sp_readerrorlog para carregar os dados em uma tabela temporária.

CREATE TABLE #Errors (
   LogDate     DATETIME,
   ProcessInfo NVARCHAR(50),    
   MessageText NVARCHAR(2000)
)

INSERT INTO #Errors
EXEC sp_readerrorlog

Passo 3: Mostrar Resultados Sem o Preenchimento de Data

Consulte a tabela temporária que armazena os dados do log de erros do SQL e conte as entradas de erro por hora. Agrupe os resultados pela coluna LogDate, agregando as datas por hora usando as funções DateAdd e DateDiff.

SELECT DateAdd(Hour, DateDiff(Hour, '19000101', e.Logdate), '19000101') as [LogDate], 
   COUNT(e.Logdate) as [Ocorrências]
FROM #Errors e 
GROUP BY DateAdd(Hour, DateDiff(Hour, '19000101', e.Logdate), '19000101')
ORDER BY [LogDate]

O resultado dessa consulta terá datetimes ausentes onde nenhum erro ocorreu.

Passo 4: Mostrar Resultados com Preenchimento de Data

Consulte a tabela temporária que armazena os dados do log de erros do SQL e faça um left join com a função de tabela dbo.Get_DateList_uft. Agrupe os resultados pela coluna DateList da função, que preencherá as lacunas de data e mostrará 0 ocorrências para as datas ausentes.

SELECT t.Datelist, COUNT(e.Logdate) as [Ocorrências]
FROM dbo.Get_DateList_uft('Hora', '2019-05-28 00:00:00', NULL) t
   LEFT JOIN #Errors e 
      ON t.Datelist = DateAdd(Hour, DateDiff(Hour, '19000101', e.Logdate), '19000101')
GROUP BY t.Datelist
ORDER BY t.Datelist

Este conjunto de resultados inclui os datetimes mostrando ocorrências = 0, preenchendo a lacuna de data.

Conclusão

Nesta demonstração, abordamos o conceito de preencher a lacuna de data no SQL Server. Também compartilhamos uma função de tabela que pode ser usada para gerar uma tabela de datas virtual. Além disso, demonstramos como retornar os resultados de um procedimento armazenado para uma tabela temporária e usar as funções combinadas DateAdd e DateDiff para agregar datas ao nível da hora. Essas técnicas podem ser úteis em várias aplicações de negócios ao analisar ocorrências de dados ao longo do tempo.

Pensamentos Adicionais

Você pode aplicar essa técnica em vários cenários de negócios, como analisar chamadas por hora, vendas por dia, paradas de rota por hora, pedidos por dia e muitos outros. As funções combinadas DateAdd e DateDiff são frequentemente usadas com ‘19000101’ representando o equivalente numérico de 0, como DateAdd(Hour, DateDiff(Hour, 0, GETDATE()), 0). O valor numérico 0 é convertido graciosamente para a data ‘1900-01-01’.