user1551957 user1551957 - 6 months ago 9
SQL Question

Pivoting on Dates Parameters in SQL for WoW Growth

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

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.