A simple revenue report needed to be generated that displays the summarized revenue dollars for each customer from a SalesDetail table. The YYMM column that holds the year and month ('0904' = April, 2009 ...). This YYMM column show be transposed and displayed as a column header in the report rather than have it display in each row of data.
Background
Each month, we get revenue new data that is appended to the SalesDetails table. At first, we simply modified the SQL Code that is generated the report data to include the new month's data. Each month our report got a bit wider, but it was nice to see all of our past data and new total revenue. Our client decided that they would rather have a no-maintenance solution that simply reports on the most recent 6 months (They didn't need to go back any further). This way they didn't have to make any report changes from month-to-month.
Using PIVOT Operator, we had the report working by hard-coding each YYMM value, but we are looking for a solution without any hard-coding. Here is the sample of SQL using hard-coded YYMM values
SELECT
CustomerNumber,
CustomerName,
[0810] as 'Oct 2008 Revenue',
[0811] as 'Nov 2008 Revenue',
[0812] as 'Dec 2008 Revenue',
[0901] as 'Jan 2009 Revenue',
[0902] as 'Feb 2009 Revenue',
[0903] as 'Mar 2009 Revenue',
[0904] as 'Apr 2009 Revenue',
[0810] + [0811] + [0812] + [0901] + [0902] + [0903] + [0904] as 'Total Revenue'
FROM
(SELECT
CustomerNumber, CustomerName, Revenue, YYMM
FROM SalesDetails
) SD
PIVOT
( SUM(Revenue) FOR YYMM IN ([0810], [0811], [0812], [0901], [0902], [0903], [0904])) as pvt
ORDER BY
CustomerNumber, CustomerName
Solution
This problem can be solved using PIVOT operator and without Hard-coding the YYMM values, but getting the column names as mentioned in the sample SQL above could be difficult.
PIVOT Operator works on fixed number of attribute values. Hence the number of values cannot be dynamic. Hence I tried work around this issue.
Here is SQL to create the Table and load sample data
CREATE TABLE dbo.SalesDetails
(
YYMM CHAR(4) NULL,
CustomerName VARCHAR(64) NULL,
CustomerNumber VARCHAR(32) NULL,
Revenue MONEY NULL
)
GO
----- Sept 2008 Sales
INSERT dbo.SalesDetails VALUES ('0809', 'Sears' ,'11111', $34.07)
INSERT dbo.SalesDetails VALUES ('0809', 'Sears' ,'11111', $24.07)
INSERT dbo.SalesDetails VALUES ('0809', 'Kmart' ,'22222', $41.11)
INSERT dbo.SalesDetails VALUES ('0809', 'Kmart' ,'22222', $1.78)
--- Oct 2008 Sales
INSERT dbo.SalesDetails VALUES ('0810', 'Sears' ,'11111', $34.99)
INSERT dbo.SalesDetails VALUES ('0810', 'Sears' ,'11111', $124.00)
INSERT dbo.SalesDetails VALUES ('0810', 'Sears' ,'11111', $11.11)
INSERT dbo.SalesDetails VALUES ('0810', 'Kmart' ,'22222', $61.78)
--- Nov 2008 Sales
INSERT dbo.SalesDetails VALUES ('0811', 'Sears' ,'11111', $84.39)
INSERT dbo.SalesDetails VALUES ('0811', 'Kmart' ,'22222', $124.00)
INSERT dbo.SalesDetails VALUES ('0811', 'Kmart' ,'22222', $11.11)
INSERT dbo.SalesDetails VALUES ('0811', 'Kmart' ,'22222', $61.78)
--- Dec 2008 Sales
INSERT dbo.SalesDetails VALUES ('0812', 'Sears' ,'11111', $2.99)
INSERT dbo.SalesDetails VALUES ('0812', 'Sears' ,'11111', $41.81)
INSERT dbo.SalesDetails VALUES ('0812', 'Kmart' ,'22222', $283.23)
--- Jan 2009 Sales
INSERT dbo.SalesDetails VALUES ('0901', 'Sears' ,'11111', $2.99)
INSERT dbo.SalesDetails VALUES ('0901', 'Sears' ,'11111', $41.81)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $43.44)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $10.34)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $29.00)
INSERT dbo.SalesDetails VALUES ('0901', 'Kmart' ,'22222', $111.34)
--- Feb 2009 Sales
INSERT dbo.SalesDetails VALUES ('0902', 'Sears' ,'11111', $2.99)
INSERT dbo.SalesDetails VALUES ('0902', 'Sears' ,'11111', $41.81)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $43.44)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $10.34)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $29.00)
INSERT dbo.SalesDetails VALUES ('0902', 'Kmart' ,'22222', $111.34)
--- Mar 2009 Sales
INSERT dbo.SalesDetails VALUES ('0903', 'Sears' ,'11111', $12.89)
INSERT dbo.SalesDetails VALUES ('0903', 'Sears' ,'11111', $21.81)
INSERT dbo.SalesDetails VALUES ('0903', 'Kmart' ,'22222', $33.40)
INSERT dbo.SalesDetails VALUES ('0903', 'Kmart' ,'22222', $110.94)
--- Apr 2009 Sales
INSERT dbo.SalesDetails VALUES ('0904', 'Sears' ,'11111', $14.89)
INSERT dbo.SalesDetails VALUES ('0904', 'Sears' ,'11111', $15.81)
INSERT dbo.SalesDetails VALUES ('0904', 'Kmart' ,'22222', $13.40)
INSERT dbo.SalesDetails VALUES ('0904', 'Kmart' ,'22222', $156.94)
Since PIVOT works on fixed set of Attributes, I used a CASE statement and passed parameters to segregate past 6 months data as [SIXTHMONTH], [FIFTHMONTH], [CURRENTMONTH] etc. I used this new fixed attributes in the PIVOT portion. Here is the solution
DECLARE @SixthLastMonth char(4)
DECLARE @FifthLastMonth char(4)
DECLARE @FourthLastMonth char(4)
DECLARE @ThirdLastMonth char(4)
DECLARE @SecondLastMonth char(4)
DECLARE @LastMonth char(4)
DECLARE @CurrentMonth char(4)
SELECT @SixthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -6, GETDATE()), 12), 1,4)
SELECT @FifthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -5, GETDATE()), 12), 1,4)
SELECT @FourthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -4, GETDATE()), 12), 1,4)
SELECT @ThirdLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -3, GETDATE()), 12), 1,4)
SELECT @SecondLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -2, GETDATE()), 12), 1,4)
SELECT @LastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -1, GETDATE()), 12), 1,4)
SELECT @CurrentMonth = SUBSTRING(CONVERT(VARCHAR, GETDATE(), 12), 1,4)
SELECT
CustomerNumber,
CustomerName,
ISNULL([SIXTHMONTH], 0.00) as 'Sixth Last Month Revenue',
ISNULL([FIFTHMONTH], 0.00) as 'Fifth Last Month Revenue',
ISNULL([FOURTHMONTH], 0.00) as 'Fourth Last Month Revenue',
ISNULL([THIRDMONTH], 0.00) as 'Third Last Month Revenue',
ISNULL([SECONDMONTH], 0.00) as 'Second Last Month Revenue',
ISNULL([LASTMONTH], 0.00) as 'Last Last Month Revenue',
ISNULL([CURRENTMONTH], 0.00) as 'Current Last Month Revenue',
ISNULL([SIXTHMONTH], 0.00) + ISNULL([FIFTHMONTH], 0.00)
+ ISNULL([FOURTHMONTH], 0.00) + ISNULL([THIRDMONTH], 0.00)
+ ISNULL([SECONDMONTH], 0.00) + ISNULL([LASTMONTH], 0.00)
+ ISNULL([CURRENTMONTH], 0.00) AS 'Total Revenue'
FROM
( SELECT
CustomerNumber,
CustomerName,
Revenue,
CASE YYMM
WHEN @SixthLastMonth THEN 'SIXTHMONTH'
WHEN @FifthLastMonth THEN 'FIFTHMONTH'
WHEN @FourthLastMonth THEN 'FOURTHMONTH'
WHEN @ThirdLastMonth THEN 'THIRDMONTH'
WHEN @SecondLastMonth THEN 'SECONDMONTH'
WHEN @LastMonth THEN 'LASTMONTH'
WHEN @CurrentMonth THEN 'CURRENTMONTH'
ELSE 'OTHER'
END [YYMM]
FROM SalesDetails
) SD
PIVOT
( SUM(Revenue) FOR YYMM IN ( [SIXTHMONTH], [FIFTHMONTH], [FOURTHMONTH], [THIRDMONTH], [SECONDMONTH], [LASTMONTH], [CURRENTMONTH])
) as pvt
ORDER BY
CustomerNumber, CustomerName
This solves the issue of Hard-coding the YYMM values. But the column names will be like “Sixth Last Month Revenue” instead of “October 2008 Revenue”. Well this can be solved by tweaking the headings expression in the SSRS reports.
Alternate Solutions
Using SSRS reports
One can use PIVOT reports to solve this problem by filtering out sales which is older than six months. The column headings expression need to be tweaked to show the values like “October 2008 Revenues” etc. Here is the Sample Screen shot
Using Dynamic SQL
Dynamic SQL can be used to solve this issue too. Here is the code.
declare @ColNameSixth char(40), @ColNameFifth char(40), @ColNameFourth char(40), @ColNameThird char(40), @ColNameSecond char(40), @ColNameLast char(40), @ColNameCurrent char(40)
select @ColNameSixth = datename(mm, dateadd(mm, -6, getdate())) + ' ' + cast(YEAR(dateadd(mm, -6, getdate())) as varchar) + ' Revenues'
select @ColNameFifth = datename(mm, dateadd(mm, -5, getdate())) + ' ' + cast(YEAR(dateadd(mm, -5, getdate())) as varchar) + ' Revenues'
select @ColNameFourth = datename(mm, dateadd(mm, -4, getdate())) + ' ' + cast(YEAR(dateadd(mm, -4, getdate())) as varchar) + ' Revenues'
select @ColNameThird = datename(mm, dateadd(mm, -3, getdate())) + ' ' + cast(YEAR(dateadd(mm, -3, getdate())) as varchar) + ' Revenues'
select @ColNameSecond = datename(mm, dateadd(mm, -2, getdate())) + ' ' + cast(YEAR(dateadd(mm, -2, getdate())) as varchar) + ' Revenues'
select @ColNameLast = datename(mm, dateadd(mm, -1, getdate())) + ' ' + cast(YEAR(dateadd(mm, -1, getdate())) as varchar) + ' Revenue'
select @ColNameCurrent = 'Current Revenues'
DECLARE @SixthLastMonth char(4), @FifthLastMonth char(4),@FourthLastMonth char(4),@ThirdLastMonth char(4),@SecondLastMonth char(4),@LastMonth char(4),@CurrentMonth char(4)
SELECT @SixthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -6, GETDATE()), 12), 1,4)
SELECT @FifthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -5, GETDATE()), 12), 1,4)
SELECT @FourthLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -4, GETDATE()), 12), 1,4)
SELECT @ThirdLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -3, GETDATE()), 12), 1,4)
SELECT @SecondLastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -2, GETDATE()), 12), 1,4)
SELECT @LastMonth = SUBSTRING(CONVERT(VARCHAR, DATEADD(mm, -1, GETDATE()), 12), 1,4)
SELECT @CurrentMonth = SUBSTRING(CONVERT(VARCHAR, GETDATE(), 12), 1,4)
DECLARE @SQL NVARCHAR(4000)
SELECT @SQL = '
SELECT
CustomerNumber,
CustomerName,
ISNULL([' + @SixthLastMonth + '], 0.00) as ''' + @ColNameSixth + ''',
ISNULL([' + @FifthLastMonth + '], 0.00) as ''' + @ColNameFifth + ''',
ISNULL([' + @FourthLastMonth + '], 0.00) as ''' + @ColNameFourth + ''',
ISNULL([' + @ThirdLastMonth + '], 0.00) as ''' + @ColNameThird + ''',
ISNULL([' + @SecondLastMonth + '], 0.00) as ''' + @ColNameSecond + ''',
ISNULL([' + @LastMonth + '], 0.00) as ''' + @ColNamelast + ''',
ISNULL([' + @CurrentMonth + '], 0.00) as ''Current Last Month Revenue'',
ISNULL([' + @SixthLastMonth + '], 0.00)
+ ISNULL([' + @FifthLastMonth + '], 0.00)
+ ISNULL([' + @FourthLastMonth + '], 0.00)
+ ISNULL([' + @ThirdLastMonth + '], 0.00)
+ ISNULL([' + @SecondLastMonth + '], 0.00)
+ ISNULL([' + @LastMonth + '], 0.00)
+ ISNULL([' + @CurrentMonth + '], 0.00) AS ''Total Revenue''
FROM
( SELECT CustomerNumber, CustomerName, Revenue, [YYMM]
FROM SalesDetails
) SD
PIVOT
( SUM(Revenue) FOR YYMM IN ( [' + @SixthLastMonth + '], [' + @FifthLastMonth + '], [' + @FourthLastMonth + '], [' + @ThirdLastMonth + '], [' + @SecondLastMonth + '], [' + @LastMonth + '], [' + @CurrentMonth + '])) as pvt
ORDER BY
CustomerNumber, CustomerName
'
EXEC(@SQL)
0 comments:
Post a Comment