timeline in cross-tab??

Luis and Mr Magoo came up with much more elegant solutions than my ugly looking one. However I tried to produce a query that would show all editions (tournaments?) and it probably needs a bit of a clean up. I also changed the original data so that the events for 2008 had dates in 2008 rather than 2012.

The results have columns for the tournament days and an initial row which shows the dates for that year

nameyearday1day2day3day4day5day6day7day8day9day10day11day12day13day14day15day16

20087-88-89-810-811-812-813-814-815-816-817-818-819-820-821-822-8

AA2008XXXX

BB2008XX

CC2008X

201227-728-729-730-731-71-82-83-84-85-86-87-88-89-810-8

AA2012XXXX

BB2012XXXX

CC2012XXX

DD2012XX

EE2012XXXXXXXX

And the query

-- Create the CTE portion for the dynamic query

DECLARE @cte AS VARCHAR(max) =

';WITH EditionDays AS (

SELECT edition_year, edition_start, DATEDIFF(dd, EDITION_START, EDITION_FINISH) EditionDays

FROM #editions

)

,EventDays AS (

SELECT event_name, event_year, DATEDIFF(dd, EVENT_START, EVENT_FINISH) + 1 EventDays, DATEDIFF(dd,EDITION_START, EVENT_START) EventOffset

FROM #events ev

INNER JOIN #editions ed ON ev.event_year = ed.edition_year

)

,EditionEvents AS (

SELECT edition_year, ted.n edition_day, CAST(DATEPART(dd,DATEADD(dd,ted.N-1,edition_start)) AS VARCHAR(2)) + ''-'' + CAST(DATEPART(mm,DATEADD(dd,ted.N-1,edition_start)) AS VARCHAR(2)) EditionDayMonth , evt.event_name, edn, edition_start

FROM EditionDays ed

CROSS APPLY (SELECT TOP(EditionDays) N FROM Tally) ted

LEFT OUTER JOIN (

SELECT event_name, event_year, n, n + eventoffset edn

FROM EventDays ev

CROSS APPLY (SELECT TOP(EventDays) N FROM Tally) tev

) evt ON evt.event_year = ed.edition_year AND evt.edn = ted.n

)'

DECLARE @sql AS VARCHAR(max)

;WITH

-- Determine Days in the Tournament

EditionDays AS (

SELECT edition_year, edition_start, DATEDIFF(dd, EDITION_START, EDITION_FINISH) EditionDays

FROM #editions

)

-- Determine days in the event and offset from start of the tournament

,EventDays AS (

SELECT event_name, event_year, DATEDIFF(dd, EVENT_START, EVENT_FINISH) + 1 EventDays, DATEDIFF(dd,EDITION_START, EVENT_START) EventOffset

FROM #events ev

INNER JOIN #editions ed ON ev.event_year = ed.edition_year

)

-- Build up a query to pivot

,EditionEvents AS (

SELECT edition_year, ted.n edition_day, evt.event_name, CAST(DATEPART(dd,DATEADD(dd,ted.N-1,edition_start)) AS VARCHAR(2)) + '-' + CAST(DATEPART(mm,DATEADD(dd,ted.N-1,edition_start)) AS VARCHAR(2)) EditionDayMonth , edn, edition_start

FROM EditionDays ed

CROSS APPLY (SELECT TOP(EditionDays) N FROM Tally) ted

LEFT OUTER JOIN (

SELECT event_name, event_year, n, n + eventoffset edn

FROM EventDays ev

CROSS APPLY (SELECT TOP(EventDays) N FROM Tally) tev

) evt ON evt.event_year = ed.edition_year and evt.edn = ted.n

)

-- build a dynamic query

SELECT @sql = @cte +

-- query for the date header

'SELECT cast('''' as nvarchar(10)) event_name, edition_year ' + q1.t + ' From EditionEvents group by edition_year ' +

'UNION ALL ' +

-- query for the event days

'SELECT event_name, edition_year ' + q2.t + ' From EditionEvents WHERE event_name <> '''' group by edition_year, event_name ' +

'order by edition_year, event_name'

FROM (

SELECT ', MAX(CASE WHEN edition_day = ' + CAST(edition_day AS VARCHAR(10)) + ' THEN EditionDayMonth ELSE '''' END) edition_day' + CAST(edition_day AS VARCHAR(10)) AS [text()]

FROM EditionEvents

GROUP BY edition_day

ORDER BY edition_day

FOR XML PATH('')

) as q1(t)

CROSS APPLY (

SELECT ', MAX(CASE WHEN edition_day = ' + CAST(edition_day AS VARCHAR(10)) + ' THEN ''X'' ELSE '''' END) edition_day' + CAST(edition_day AS VARCHAR(10)) AS [text()]

FROM EditionEvents

GROUP BY edition_day

ORDER BY edition_day

FOR XML PATH('')

) as q2(t)

EXEC(@sql)

If you have more than one tournament per year you will need to change the event table to have an edition_id and join and group on that rather than edition_year

To all repliers,

Due to the time difference I just saw your proposals: I must say they look great! I am going to examine them and I hope to give a reaction to you all today (is your night).

I hope that Jeff Moden is not lost on his way home. The other possibility is that he was so impressed by the given replies that his reply got redundant.

Who knows?

That some of my data were wrong surprises me because I tested them before sending to sql server central. I am sorry for the inconvenience.

Thanks to you all.

Robert

Sorry, but I now see what you mean by wrong data. The 2008 dates are mistakenly set in 2012. I try to repair that.

Robert

Examining the several solutions offered by Luis Cazares, mister.magoo and SSC Veteran they have one thing in common: I don't fully understand them. Because that's my problem we should not talk about that. I think that the first two solutions have much in common, but the one Luis offers is (a bit) more efficient. SSC Veteran chooses a different approach which works fine too, but is less efficient than the other two.

Because the lay-out is by tournament a year will be necessary as a parameter, so to me it seems logic that I use a stored procedure. I expect you can agree with that. Furthermore I hope that eventually I will understand the exact meaning of the code of Luis, but that takes time for a newbee.

Thanks for your efforts and compliments for every presented solution.

Robert

It's good to know the solution worked fine. However, you should understand it before using it.

Two tips I can give you are to use the PRINT command I included but left commented and read this article from Jeff Moden: Cross Tabs and Pivots, Part 2[/url]

If you still have doubts on the code, feel free to ask.

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.

Thanks a lot Steve: I understand you solution a little bit better than the others, but the subject stays difficult for me. When I look into the messages as a result of running your solution I see a perfect (dynamic) table. Is it possible to export that table to a webpage (eg Visual Web .aspx)? I want to show the results in a gridview, but the SQL-variable is of course no table with rows. In fact is your solution in the end a table and could be the final product of a stored procedure.

The (silly) question I still have is: is the above mentioned possible or am I talking rubbish?

Any answers/reactions would be appreciated.

Above all: compliments for your solution - I gonna try to understand it.

Robert

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..

My apologies. Right after I made the promise, we got nailed with a string of problems at work. I worked until 2:30 in the morning that first night and until 11:30 last night.

I'm taking a look at the solutions offered now. You've got some good people working on it.

Jeff,

I was just kidding of course: there are no obligations what so ever related to this kind of forums.

I agree: I have got some great solutions which are, that is to me, difficult to understand. The problem is (see Steve's posts) that at the end a cross tab is produced in sql server but presented in most cases as a variable which is then executed and the result is perfect in SQL Server environment. The last step to me is: how to export the table to visual web developer or better a webpage? I want to populate a gridview with the results and the necessary input is then a table. That table should be offered via a stored procedure (Steve produced one) and transferred to a webpage.

In a normal crosstab the table itself is to be seen in the stored procedure, but in this case not. May be because of the dynamic character because the events and the 'X' with dates differ by edition.

This is a bit beyond sql server, but may be you recognise this problem. If not, I have to try it somewhere else. I still think (and hope) that the solution can be offered in Sql Server in producing a stored procedure with a table result. Unfortunately I am to much a newbee (or something else) to figure it our myself...

Grz,

Robert