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.
