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