T-SQL: Create Report for Last 10 Years of Data
Recently in the MSDN Transact-SQL forum thread Please help with dynamic pivot query/ CTE/ SSRS we provided a solution for a very common scenario of generating a report for the last n (10 in that particular case) years (months, days, hours, etc.) of data.
Problem Definition
In the course of the thread the topic starter has provided the following definitions of the tables:
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
Given these three tables of data we wanted the following output (click the link below to view it in a separate window):
Solution
The idea here is to use the dynamic PIVOT. To generate the last 10 years of data we are going to use a loop. The reason for a simple direct loop instead of the more commonly used scenario of querying the table and generating a column list using XML path solution is that:
1) In theory, we may have missing data in our table (this is a more theoretical situation with years, but not uncommon with months or days).
2) A direct loop allows us to be more flexible and add more columns if needed. It is easy to adjust the solution to not only show year column, but also a percent difference between this year and prior year, for example.
DECLARE @StartDate DATETIME
,@EndDate DATETIME
SET @StartDate = dateadd(year, - 10 + datediff(year, '19000101', CURRENT_TIMESTAMP), '19000101');
SET @EndDate = dateadd(year, 11, @StartDate); -- this is used as an open end range date, thus I am adding 11 years
DECLARE @Columns NVARCHAR(max)
,@Year INT;
SET @Columns = '';
SET @Year = datepart(year, CURRENT_TIMESTAMP) - 10; -- starting year
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
So, you can see we used dynamic PIVOT to generate desired output and the sp_ExecuteSQL system stored procedure to run our query with two date parameters.
Update: Interestingly, we got another similar case of dynamic CROSS-TAB query in the following Transact-SQL MSDN thread Is this possible? Crosstab query. Let's show the solution along with the sample data creation. The idea of the solution is very similar to what is discussed above:
CREATE TABLE #Trans (
trans_year INT NULL
,customer_name NVARCHAR(100) NULL
,Amount FLOAT
,Quantity FLOAT
)
INSERT INTO #Trans
VALUES (
2010
,'ABC'
,100
,200
)
INSERT INTO #Trans
VALUES (
2011
,'ABC'
,200
,100
)
INSERT INTO #Trans
VALUES (
2012
,'ABC'
,500
,100
)
INSERT INTO #Trans
VALUES (
2013
,'ABC'
,400
,700
)
INSERT INTO #Trans
VALUES (
2014
,'ABC'
,600
,800
)
DECLARE @StartYear INT
,@EndYear INT;
SELECT @StartYear = MIN(trans_year)
,@EndYear = MAX(trans_year)
FROM #Trans;
DECLARE @Columns NVARCHAR(max)
,@Year INT;
SET @Columns = '';
SET @Year = @StartYear
WHILE @Year <= @EndYear
BEGIN
SET @Columns = @Columns + N',
SUM(CASE WHEN trans_year = ' + CAST(@year AS NVARCHAR(max)) + ' THEN Amount END) AS ' + quotename(cast(@year AS NVARCHAR(max))) + CASE
WHEN @Year > @StartYear
THEN N',
SUM(CASE WHEN trans_year <= ' + CAST(@year AS NVARCHAR(max)) + ' THEN Amount END) AS ' + quotename('Total up to ' + cast(@year AS NVARCHAR(max)))
ELSE ''
END;
SET @Year = @Year + 1;
END
SET @Columns = STUFF(@Columns, 1, 2, '');
DECLARE @SQL NVARCHAR(max);
SET @SQL = ';WITH CTE AS (SELECT trans_year, customer_name, Amount FROM #Trans
WHERE trans_year >= @StartYear and trans_year <= @EndYear)
SELECT Customer_Name, ' + @Columns + '
FROM cte GROUP BY Customer_Name'
PRINT @SQL;
EXECUTE sp_ExecuteSQL @SQL
,N'@StartYear INT, @EndYear INT'
,@StartYear
,@EndYear
DROP TABLE #Trans;
Interestingly, there is yet another forum's topic on this popular problem. We will only add a reference to that thread:
Required Output for Quarter
Conclusion
We showed how easily we can generate a report for last n years (months, days, hours) of data and how easily we can add more columns to the output using direct loop solution.
See Also
- [[Transact-SQL Portal]]
- T-SQL: Dynamic Pivot on Multiple Columns
Others Languages
This article participated in November TechNet Guru Contributions and won the gold prize.