A slight alternative that might be easier to understand:

Sample data:

If Object_Id('TempDB..#Editions', 'U') Is Not Null

Drop Table #Editions

Create Table #Editions

(

Edition_ID Int Primary Key Clustered,

Edition_Year int,

Edition_Start date ,

Edition_Finish date

)

Set Dateformat DMY

Insert into #Editions

(Edition_ID, Edition_Year, Edition_Start, Edition_Finish)

Select '1', '2012', '27-07-2012', '11-08-2012' UNION ALL

Select '2', '2008', '07-08-2008', '23-08-2008'

-- second table #Events

If Object_Id('TempDB..#Events', 'U') Is Not Null

Drop Table #Events

Create Table #Events

(

Event_ID Int Primary Key Clustered,

Event_Name nvarchar(10),

Event_Year int,

Event_Start date ,

Event_Finish date

)

Set Dateformat DMY

Insert into #Events (Event_ID, Event_Name, Event_Year, Event_Start, Event_Finish)

Select '1', 'AA', '2012', '04-08-2012','07-08-2012' UNION ALL

Select '2', 'BB', '2012', '07-08-2012','10-08-2012' UNION ALL

Select '3', 'CC', '2012', '04-08-2012','06-08-2012' UNION ALL

Select '4', 'DD', '2012', '31-07-2012','01-08-2012' UNION ALL

Select '5', 'EE', '2012', '27-07-2012','03-08-2012' UNION ALL

Select '6', 'AA', '2008', '17-08-2008','20-08-2012' UNION ALL

Select '7', 'BB', '2008', '12-08-2008','13-08-2012' UNION ALL

Select '8', 'CC', '2008', '20-08-2008','20-08-2012' union all

Select '9', 'ZZ', '2012', '12-08-2012','12-08-2012' --- extra row to prove that gaps work

The following will only work for a defined year:

Declare @SelectedYearchar(4)

,@SqlCmdvarchar(8000)

,@Columnsvarchar(8000)

,@min_Datedate

,@max_Datedate;

set @SelectedYear = '2012';

-- Work out the Data range

select@min_Date = min( Event_Start )

,@max_Date = max ( Event_Finish )

from #Events

where Event_Year = @SelectedYear ;

-- Create a table to hold each day offset

Declare @Tally table ( Number int )

insert @Tally ( Number )

select Number

from [master].[dbo].[spt_Values]

where Number between 0 and (select datediff( dd , @min_Date , @max_Date ) )

-- Build up a column list that will determine whether or not the event occurs on that day

Set @Columns = '' ;

Select @Columns = @Columns + ' , CASE WHEN ''' + Event_Day +''' '

+ 'BETWEEN [Event_Start] AND [Event_Finish] THEN ''X'' ELSE '''' END AS ['+Event_Day_Label+']' + char(13) + char(10)

from (

Select distinct top 100 percent

Event_Day= cast( dateAdd ( dd , Number , @min_Date ) as varchar(20) )

,Event_Day_Label= convert ( char(5), dateAdd ( dd , Number , @min_Date ), 105 )

,Number

from @Tally

order by Number

) as x

--- Create a string that holds the query

Select @SqlCmd =

'Select Event_Name , Event_Year ' + char(13) + char(10)

+ @Columns

+ 'from #Events ' + char(13) + char(10)

+ 'where Event_Year = ' + @SelectedYear + char(13) + char(10)

+ 'order by Event_Name' + char(13) + char(10)

-- Show the dynamic query

print @SqlCmd

-- Run the query

exec ( @SqlCmd )

Hope this helps.