T-SQL: Criando Relatório de Dados dos últimos 10 anos
Recentemente, no fórum MSDN Transact-SQL, o tópico Por favor, ajudem com consulta PIVOT dinâmica / CTE / SSRS, eu forneci uma solução para um cenário muito comum de geração de relatório para os últimos N (10, nesse caso em particular) anos (meses, dias, horas, etc .) de dados.
Definição do Problema
No decorrer do tópico um post proporcionou as seguintes definições das tabelas:
CREATE TABLE [dbo].[_Records](
[ID] [varchar](255) NULL,
[FirstName] [varchar](255) NULL,
[LastName] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[_Records]
([ID], [FirstName], [LastName])
VALUES
('1', 'A1', 'B1'),
('2', 'A2', 'B2'),
('3', 'A3', 'B3'),
('4', 'A4', 'B4'),
('5', 'A5', 'B5')
GO
CREATE TABLE [dbo].[_RecordDetails](
[RecordID] [varchar](255) NULL,
[Address] [varchar](255) NULL,
[Phone] [varchar](255) NULL
) ON [PRIMARY]
GO
INSERT INTO [dbo].[_RecordDetails]
([RecordID]
,[Address]
,[Phone])
VALUES
('1', 'Add1', 'P1'),
('2', 'Add2', 'P2'),
('3', 'Add3', 'P3'),
('4', 'Add4', 'P4'),
('5', 'Add5', 'P5')
GO
CREATE TABLE [dbo].[_Money](
[RecordID] [varchar](255) NULL,
[Date] [varchar](255) NULL,
[Amount] [numeric](20, 4) NOT NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[_Money]
([RecordID]
,[Date]
,[Amount])
VALUES
('1', '1/1/2004', '5'),
('1', '2/1/2004', '10'),
('1', '4/1/2006', '4'),
('1', '6/1/2007', '6'),
('1', '3/1/2010', '8'),
('2', '3/1/2004', '4'),
('2', '4/1/2004', '6'),
('2', '5/1/2005', '7'),
('2', '6/1/2011', '8'),
('3', '1/1/2005', '5'),
('3', '2/1/2005', '10'),
('3', '3/1/2007', '4'),
('3', '4/1/2008', '6'),
('3', '5/1/2008', '8'),
('3', '6/1/2009', '4'),
('3', '7/1/2012', '6'),
('3', '8/1/2012', '7'),
('3', '9/1/2012', '8'),
('4', '1/1/2006', '5'),
('4', '2/1/2006', '10'),
('4', '3/1/2008', '4'),
('4', '4/1/2008', '6'),
('4', '5/1/2008', '8'),
('4', '6/1/2010', '4'),
('4', '7/1/2011', '6'),
('4', '8/1/2011', '7'),
('4', '9/1/2011', '8'),
('4', '10/1/2012', '5'),
('4', '11/1/2012', '10'),
('4', '7/1/2013', '4'),
('4', '8/1/2013', '6'),
('4', '9/1/2013', '8'),
('5', '4/1/2008', '4'),
('5', '6/1/2010', '6'),
('5', '6/1/2011', '7'),
('5', '7/1/2011', '8'),
('5', '8/1/2011', '5'),
('5', '9/1/2012', '10'),
('5', '10/1/2012', '4'),
('5', '11/1/2013', '6'),
('5', '7/1/2013', '8'),
('5', '8/1/2013', '4'),
('5', '9/1/2013', '6'),
('5', '10/1/2013', '7'),
('5', '11/1/2013', '8')
GO
Tendo em conta estas 3 tabelas com dados, nós procuramos o seguinte resultado (clique no link abaixo para vê-lo em uma janela separada):
[Ver: http://social.technet.microsoft.com/Forums/getfile/374747:550:0 ]
Solução
A idéia aqui é usar o PIVOT dinâmico. Para gerar os últimos 10 anos de dados, eu vou usar um loop. A razão que eu estou usando um loop simples em vez de um cenário mais comumente usado de consultar a tabela e gerar uma lista de coluna usando uma solução de XML path, é que:
1) Em teoria podemos ter falta de dados em nossa tabela (esta é uma situação mais teórica com anos, mas não é incomum com meses ou dias);
2) O loop direto nos permite ser mais flexíveis e adicionar mais colunas, se necessário. Me fale, é fácil de ajustar solução não só para mostrar a coluna de ano, mas também uma diferença percentual entre este ano e o ano anterior, por exemplo.
DECLARE @StartDate DATETIME
,@EndDate DATETIME
SET @StartDate = dateadd(YEAR, - 10 + datediff(YEAR, '19000101', CURRENT_TIMESTAMP), '19000101');
SET @EndDate = dateadd(YEAR, 11, @StartDate); -- isto é usado como um "RANGE" de datas aberta, assim eu estou adicionando 11 anos
DECLARE @Columns NVARCHAR(max)
,@year INT;
SET @Columns = '';
SET @year = datepart(YEAR, CURRENT_TIMESTAMP) - 10; -- iniciando o ano
WHILE @year <= datepart(YEAR, CURRENT_TIMESTAMP)
BEGIN
SET @Columns = @Columns + ', ' + quotename(CAST(@year AS NVARCHAR(max)))
SET @year = @year + 1;
END
SET @Columns = STUFF(@Columns, 1, 2, '');
--SELECT @Columns;
DECLARE @SQL nvarchar(max);
SET @SQL = ';WITH CTE AS (SELECT R.[ID], R.[FirstName], R.[LastName], RD.Address, RD.Phone
FROM dbo._Records R LEFT JOIN dbo._RecordDetails RD on R.ID = RD.RecordID),
cte2 AS
(SELECT cte.ID, cte.FirstName, cte.LastName, cte.Address, cte.Phone, M.RecordID, datepart(year,M.[Date]) as yDate, M.Amount
FROM CTE INNER JOIN dbo._Money M ON cte.ID = M.RecordID
WHERE M.[Date] >=@StartDate and M.Date < @EndDate)
SELECT * FROM cte2 PIVOT (SUM(Amount) FOR yDate IN (' + @Columns + ')) pvt'
EXECUTE sp_ExecuteSQL @SQL, N'@StartDate datetime, @EndDate datetime', @StartDate, @EndDate
Assim, você pode ver que usamos PIVOT dinâmico para gerar o resultado desejado e então o procedimento armazenado (stored procedure) do sistema sp_executesql para executar a nossa consulta, com dois parâmetros de data.
Conclusão
Mostrei como facilmente podemos gerar um relatório dos últimos "N" anos (meses, dias, horas) de dados e como facilmente podemos adicionar mais colunas para a saída usando a solução de loop direto.
Veja Também
- [[Portal Transact-SQL]]
- Pivot dinâmico com múltiplas colunas