Dicas T-SQL – CTE

Vamos apresentar hoje mais uma dica de escrita de código para resolver problemas comuns no dia a dia de quem escreve consultas em T-SQL.

Imagine que você precisa consultar valores por data, onde seja necessário trazer também as datas em que não há valores. Uma forma de atingir este objetivo é gerar uma lista de datas sequenciais. Você pode gerar esses dados de forma recursiva utilizando Common Table Expression (CTE) para apoiar nesse tipo de necessidade.

Para criarmos uma consulta recursiva, usamos três elementos:

Caso base
Chamada recursiva com a cláusula UNION ALL
Condição de parada

Aplicando estes conceitos à nossa necessidade de gerar dados sequenciais podemos adotar, por exemplo:

Caso base – 1 como sendo o Número
Chamada recursiva – Número + 1
Condição de parada – Número < 100

O código produzido:

WITH CTE AS (
  SELECT 1 AS Numero

  UNION ALL

  SELECT Numero + 1
  FROM CTE
  WHERE Numero < 100
)
SELECT * FROM CTE;

Tratando com datas, podemos adotar o seguinte:

Caso base – minha data inicial como sendo a Data
Chamada recursiva – Data + 1 dia
Condição de parada – Data < minha data final

Imagine que nós precisamos gerar datas dos próximos 365 dias:

WITH CTE AS (
  SELECT CAST(DATEADD(DAY, 1, GETDATE()) AS DATE) AS Data

  UNION ALL

  SELECT DATEADD(DAY, 1, Data)
  FROM CTE
  WHERE Data < DATEADD(DAY, 365, CAST(GETDATE() AS DATE))
)
SELECT * FROM CTE
OPTION (MAXRECURSION 365);

Observação: Por padrão o MAXRECURSION assume o valor 100, como precisamos gerar 365 dias precisamos alterar essa opção na consulta. Caso você queira o número máximo de recursões permitido, deve passar (MAXRECURSION 0).

Trazendo agora para um problema real, vamos gerar as datas dos próximos 12 meses a partir de uma determinada data:

DECLARE @DataInicial DATETIME; 
SET @DataInicial = '2014-01-01 '; 

WITH CTE AS ( 

  SELECT DATEADD(DAY, 1, EOMONTH(@DataInicial,-1)) DataInicio, EOMONTH(@DataInicial) AS DataFim 
  UNION ALL 
  SELECT DATEADD(DAY, 1, EOMONTH(DATEADD(DAY, 1, DataFim),-1)) DataInicio, EOMONTH(DATEADD(MONTH, 1, DataFim)) AS DataFim 
  FROM CTE 
  WHERE DataFim < DATEADD(MONTH, 12, @DataInicial) 
) 
SELECT * FROM CTE;

Agora vamos relacionar esta CTE com nossos valores. Para isso utilizaremos a base AdventureWorks2017:

USE AdventureWorks2017
GO

DECLARE @DataInicial DATETIME; 
SET @DataInicial = '2014-01-01 '; 
 
WITH CTE AS ( 
 
  SELECT DATEADD(DAY, 1, EOMONTH(@DataInicial,-1)) DataInicio, EOMONTH(@DataInicial) AS DataFim 
  UNION ALL 
  SELECT DATEADD(DAY, 1, EOMONTH(DATEADD(DAY, 1, DataFim),-1)) DataInicio, EOMONTH(DATEADD(MONTH, 1, DataFim)) AS DataFim 
  FROM CTE 
  WHERE DataFim < DATEADD(MONTH, 12, @DataInicial) 
) 
SELECT FORMAT(CTE.DataInicio, 'yyyy-MM') AS AnoMes 
  ,SUM(ISNULL(S.TotalDue, 0.)) AS Valor 
FROM CTE  
LEFT JOIN Sales.SalesOrderHeader S ON S.OrderDate >= CTE.DataInicio AND S.OrderDate < DATEADD(DAY,1,CTE.DataFim)
GROUP BY FORMAT(CTE.DataInicio, 'yyyy-MM')
ORDER BY AnoMes;

O resultado produzido será:

Dúvida sobre como utilizar essa abordagem? Deixe um comentário.

Deixe um comentário Cancelar resposta