Cross Tab Or Pivot Issue

I have the following query:




Code Snippet
DECLARE @start_date datetime,
@weeks int

SET @start_date = '1/1/2007'/*Set this to the date you want your analysis to begin on. Be mindful of the date you pick. For instance, in this case we want the
beginning of the week to always be a Monday so we are OK picking 1/1/2007 because it is a Monday.*/
SET @weeks = 12

SELECT DATEADD(dd, (n.Num-1) * 7, @start_date) [Beginning of Week], /*This is creating the Beginning of each week. Looks at each record in the Number table,
subtracts one off of the Num field and then multiplies by 7. Takes the resulting number and adds that many days to our start date variable.*/
DATEADD(dd,(n.Num * 7)-1,@start_date) [End of Week], /*This is creating the Ending of each week. Looks at each record in the Number table,
muiltplies it by 7 and then subtracts one off of the result. Takes the resulting number and adds that many days to our start date varaible.*/
( /*This is where we are counting the number of opportunities in each week. This returns a reasulting count for each combination that is
generated in the outer query.*/
SELECT COUNT(*)
FROM op_opportunity op_o
WHERE op_o.open_date BETWEEN DATEADD(dd, (n.Num-1) * 7 , @start_date) AND DATEADD(dd, (n. Num * 7)-1, @start_date) /*Count if the
open date is between
what we've identified as the Beginning of Week and End of Week for each iteration.*/
) AS [Opportunities Open]
FROM Numbers n
WHERE DATEADD(dd,n. Num * 7,@start_date) >= DATEADD(wk, -@weeks+1, GETDATE()) AND DATEADD(dd,n. Num * 7,@start_date) <= DATEADD(wk, 1, GETDATE())
/*We are just limiting our evaluation to anything less than todays date and within the number of weeks in our @week variable.*/






It returns a result set that looks like this:



Code Snippet
Beginning of Week End of Week Opportunities Open
11/5/2007 12:00:00 AM 11/11/2007 12:00:00 AM 369
11/12/2007 12:00:00 AM 11/18/2007 12:00:00 AM 326
11/19/2007 12:00:00 AM 11/25/2007 12:00:00 AM 203
11/26/2007 12:00:00 AM 12/2/2007 12:00:00 AM 333
12/3/2007 12:00:00 AM 12/9/2007 12:00:00 AM 421
12/10/2007 12:00:00 AM 12/16/2007 12:00:00 AM 286
12/17/2007 12:00:00 AM 12/23/2007 12:00:00 AM 411
12/24/2007 12:00:00 AM 12/30/2007 12:00:00 AM 48
12/31/2007 12:00:00 AM 1/6/2008 12:00:00 AM 234
1/7/2008 12:00:00 AM 1/13/2008 12:00:00 AM 314
1/14/2008 12:00:00 AM 1/20/2008 12:00:00 AM 309
1/21/2008 12:00:00 AM 1/27/2008 12:00:00 AM 207




What I want to do is change this so that I can get a count of new opportunties by week but also by rep. In the op_opportunity table there is a field called sales_owner that I would use. Ideally, I also want to have it Pivoted so that the weeks are columns and the sales_owner are the rows. I tried using the PIVOT command but you have to explicitly indicate your columns. Because the number of weeks that are chosen could always be different I can't do that.

Ultimately, this will be presented through reporting services so I can probably use the Cross Tab tool to show it the way I want, so if I could just get the same result set but instead the rep added and the opportunities they created that would probably work. So if we had 12 weeks and 10 reps that would produce 120 rows.

I also hardcoded the @startdate to 1/1/07 because that is a Monday and it is back far enough in the past that I don't have to worry. As we get further away from that date will that cause any performance issues?

Any input would be helpful. Thanks.