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.