Pivoting on Dates Parameters in SQL for WoW Growth

Question

I am trying to measure and display week over week growth within a SSRS report.

I am trying to use a combination of the pivot function within SQL and parameters to easily retrieve the data I need in SQL without having to add overcomplicated formulas and matrixes in SSRS.

The formula I tried is:

SELECT     category, COALESCE (sum([@wedate]), 0) AS currentweek, COALESCE (sum([@wedate-7]), 0) AS previousweek
FROM OfficeProduction 
PIVOT (sum(amount) FOR wedate IN ([@wedate], [@wedate-7])) AS p
WHERE     category = 'Revenue'
GROUP BY category

When i try to save this as a stored proc I get errors, as well as when i insert it directly into the dataset box within SSRS.

I have tested inserting values for the parameters

SELECT     category, COALESCE (sum([7/7/12]), 0) AS currentweek, COALESCE (sum([6/30/12]), 0) AS previousweek

FROM OfficeProduction 

PIVOT (sum(amount) FOR wedate IN ([7/7/12], [6/30/12])) AS p
WHERE     category = 'Revenue'
GROUP BY category

and I get the appropriate result...so i get the feeling im close, any help would be appreciated.

Answer 1

It sounds like you will need Dynamic SQL to pass in your parameters and execute the final statement with your parameter value. Here is a short quick script that should work:

create table t
(
    id int,
    wedate datetime,
    amount int
)

insert into t values (1, '2012-07-07', 50)
insert into t values (2, '2012-06-30', 25)
insert into t values (3, '2012-07-07', 75)
insert into t values (4, '2012-06-30', 25)

DECLARE @wedate datetime
DECLARE @wedateP datetime
declare @sql varchar(max)

DECLARE @wedateCol varchar(10)
DECLARE @wedatePCol varchar(10)

set @wedate = '2012-07-07'
set @wedateP = DateAdd(d, -7, @wedate)

set @wedateCol = Convert(char(10), @wedate, 101)
set @wedatePCol = Convert(char(10), @wedateP, 101)

set @sql = 'select * FROM t
            PIVOT
            (
                sum(amount)
                for wedate in ([' + @wedateCol + '], [' + @wedatePCol +' ])
            )p '

exec(@sql)

drop table t

It is creating the week previous value in a separate parameter and then converting those to text values for the PIVOT.