To turn into a stored procedure then its simple:
create proc sp_GetEventOccurances @SelectedYearchar(4)
as
begin
Declare@SqlCmdvarchar(8000)
,@Columnsvarchar(8000)
,@min_Datedate
,@max_Datedate;
-- 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 )
end
Then to call it:
sp_GetEventOccurances @SelectedYear = '2012'
You can certainly call a stored procedure via ado.net or Linq
Iterate through the result set and add columns dynamically and then populate the cells in a gridview.
An alternative is if you know that all the events last X days then you can then use a pivot table or a fixed query to return the data. Make it easy to write and code but you will not have the flexability when events are extended.
Hope this helps..