T-SQL: Use A Variable to Create a ColumnList for a PIVOT Query of Dates

DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '7/1/2010';
SET @EndDate = '7/31/2010';

--Create a dates table with every date between 7/1/2010 and 7/31/2010
WITH mycte AS
(
SELECT cast(@BeginDate as datetime) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < @EndDate

)
SELECT CONVERT(varchar,DateValue,101) as StDate INTO #dates from mycte
OPTION (MAXRECURSION 0);

--Get each date in the #dates table as a set
select ',[' + StDate + ']' from #dates;

--Get it as a long string. We use the XML path for that, which is awesome.
select ',[' + StDate + ']' from #dates for XML path('');

--Use the STUFF function to remove the leading comma. This is a pretty cool way to do that, rather than messing around with LEN and stuff.
SELECT STUFF((select ',[' + StDate + ']' from #dates for XML path('')),1,1,'') AS NoLeadingCommaOneLongString;

--Use the same query to set a variable value.
DECLARE @ColumnList varchar(max);
SET @ColumnList = STUFF((select ',[' + StDate + ']' from #dates for XML path('')),1,1,'');

--Here's a sample table with dates and names.
CREATE TABLE #SampleWorkDates
(
Workeddate datetime
, [name] varchar(100)
, workdone int
);
INSERT INTO #SampleWorkDates
SELECT '7/12/2010','john', 100 UNION
SELECT '7/12/2010','john', 110 UNION
SELECT '7/12/2010','debby', 102 UNION
SELECT '7/18/2010','john', 60 UNION
SELECT '8/10/2010','alex', 89 UNION
SELECT '9/11/2010','john', 120;

--We need to pivot that data, but the column list is really long.

--Rather than writing out all the columns by hand, like this:
select * from
(select [Name],count([Name]) over (partition by [Name]) as Total,CONVERT(varchar,[WorkedDate],101) as Workeddate,COUNT(*) as cnt from #SampleWorkDates
where CAST(WorkedDate AS datetime) >= '07/01/2010' and CAST(WorkedDate AS datetime) <= '07/31/2010' group by [Name],CONVERT(varchar,[WorkedDate],101))p
pivot (MAX([name]) for WorkedDate in ([07/01/2010],[07/02/2010],[07/03/2010],[07/04/2010],[07/05/2010],[07/06/2010],[07/07/2010],[07/08/2010],[07/09/2010],[07/10/2010],[07/11/2010],[07/12/2010],[07/13/2010],[07/14/2010],[07/15/2010],[07/16/2010],[07/17/2010],[07/18/2010],[07/19/2010],[07/20/2010],[07/21/2010],[07/22/2010],[07/23/2010],[07/24/2010],[07/25/2010],[07/26/2010],[07/27/2010],[07/28/2010],[07/29/2010],[07/30/2010]))pvt


--We can just use the @ColumnList variable we used before. It looks cleaner and is reusable.
declare @sql nvarchar(max);
set @sql = 'select * from (select [Name],count([Name]) over (partition by [Name]) as Total,CONVERT(varchar,[WorkedDate],101) as Workeddate,COUNT(*) as cnt from #SampleWorkDates
where CAST(WorkedDate AS datetime) >= ''' + CONVERT(varchar, @BeginDate, 101) + ''' and CAST(WorkedDate AS datetime) <= ''' + CONVERT(varchar, @EndDate, 101) +
''' group by [Name],CONVERT(varchar,[WorkedDate],101))p
pivot (sum(cnt) for WorkedDate in (' + @ColumnList + '))pvt';

PRINT @sql;

exec(@sql);


DROP TABLE #SampleWorkDates;
DROP TABLE #dates;

T-SQL: Script to Populate a Dates Table With Recursion and a CTE

I used to create dates tables like this:

--********************************
DECLARE @BeginDate datetime
DECLARE @EndDate datetime

SET @BeginDate = '1/1/1900'
SET @EndDate = '12/31/2050'

CREATE TABLE #Dates ([date] datetime)

WHILE @BeginDate <= @EndDate
BEGIN
INSERT #Dates
VALUES
(@BeginDate)

SET @BeginDate = @BeginDate + 1
END
SELECT * FROM #Dates
DROP TABLE #Dates
--********************************

That works fine, but it has a lot to clean up, executes slowly, and has a lot of code. Plus it uses looping.

This is a better way:
--********************************
WITH mycte AS
(
SELECT cast('1900-01-01' as datetime) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < '2050-12-31'

)
SELECT DateValue
FROM mycte
OPTION (MAXRECURSION 0)
--********************************

By default, the MAXRECURSION option is set to 100. I set it to zero, which means we could go into an infinite loop. I use the WHERE clause to make sure the loop bails out at 12/31/2050. Without the WHERE clause, we'd be hosed.

SSRS 2008 - Adding SSL After Installation

If you can't get it to work, try doing everything in this thread:

http://prologika.com/CS/forums/t/946.aspx

And everything here:

http://technet.microsoft.com/en-us/library/ms345223(SQL.100).aspx


Also, SSRS 2008 does not support wildcard certificates, ie *.yourdomain.com. Obtain a special dedicated SSL cert YourServerName.yourdomain.com, then apply it on the SSRS 2008 server. This is what worked for us at a client, after calling PSS. This is a known issue at Microsoft and they are working on a better solution.