timeline in cross-tab??

  • Hello experts,

    My problem is as follows. On one hand I have got the beginning and end date of a tournement, on the other hand I have the beginning and end dates of the several events and the name of the events within that tournement.

    The end result should be a sort of time schedule disguised as a cross tab. On the y-ax there are the names of the several events, on the x-ax there are the tournement dates (eg from 2012-07-27 to 2012-08-11). In the middle of the cross-tab we can see the timelines related to the several events.

    In the database we have a table Editions and a table Events (details below). Is this question an impossible question or the contrary?

    Reference data:

    -- first table #Editions

    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 datetime,

    Edition_Finish datetime

    )

    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 datetime,

    Event_Finish datetime

    )

    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-2008' UNION ALL

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

    Select '8', 'CC', '2008', '20-08-2008','20-08-2008'

    To give you some idea of the meant end-result (unfortunately in Excel) the following table

    for the year 2012:

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

    AAXXXX

    BBXXXX

    CCXXX

    DDXX

    EEXXXXXXXX

    I left the year out in the top and the 'X' can be anything else, eg a black or colourful square.

    When the above is possible I want to end up with an in-database solution eg stored procedure or function. I don't know which form is the best.

    Is there anyone out there who can help me out?

    Thanks in advance,

    Robert.

  • Well done on posting readily consumable data and a good description of the problem. I suspect that, unlike what my article says on the subject of posting readily consumable data, that the problem is just a bit complex for most. I'll be on may way home in about an hour and I'll give it a turn after dinner.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Can you explain what role #Editions will play in producing the results?

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • This is mostly a dynamic cross-tab. It's not that hard when you understand them, but you need to be really careful on what you're doing.

    Please, test the code and be sure to understand it. You might need to change somethings but this can give you an idea.

    By the way, your data had some dates wrong.

    DECLARE @Edition_Yearint

    DECLARE @SQLnvarchar( 4000) = '';

    SET @Edition_Year = '2012';

    WITH Edition_Calendar AS(

    SELECT DATEADD( DAY, t.N -1, ed.Edition_Start) dates

    FROM #Editions ed

    CROSS

    JOIN dbo.Tally t

    WHERE ed.Edition_Year = @Edition_Year

    AND ed.Edition_Finish >= DATEADD( DAY, t.N -1, ed.Edition_Start)),

    SQL_String( String) AS(

    SELECT ',CASE WHEN ''' + CONVERT( char(8), dates, 112) + ''' BETWEEN ev.Event_Start AND ev.Event_Finish ' + CHAR(10) +

    'THEN ''X'' ELSE '''' END AS ''' + CONVERT( char(5), dates, 5) + '''' + CHAR(10)

    FROM Edition_Calendar

    FOR XML PATH(''))

    SELECT @SQL = String FROM SQL_String

    SET @SQL = 'SELECT ev.Event_Name ' + CHAR(10) + @SQL +

    'FROM #Events ev ' + CHAR(10) +

    'WHERE ev.event_year = @Year '

    --PRINT @SQL

    EXEC sp_executesql @SQL, N'@Year int', @Year = @Edition_Year

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I wrote it now, so despite Luis excellent answer I am posting it - seems a waste otherwise 😛

    -- OP Setup Code --

    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 datetime,

    Edition_Finish datetime

    )

    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 datetime,

    Event_Finish datetime

    )

    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-2012','20-08-2012' UNION ALL

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

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

    -- End OP Setup Code

    DECLARE @SelectedYear CHAR(4) = '2012';

    -- Make sure we don't get any errors with a temp table

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

    Drop Table #days

    -- Generate a list of days for the selected Edition

    select dateadd(day,T.N,ed.edition_start) as Event_Day,CONVERT(char(5),dateadd(day,T.N,ed.edition_start),5) as Event_Day_Label

    into #days

    from #Editions ed

    join Tally T

    on T.N between 1 and datediff(day,ed.Edition_Start,ed.Edition_Finish)

    where ed.Edition_Year=@SelectedYear

    -- Generate a SELECT column list that will populate the Xs

    declare @cols varchar(max)='';

    SELECT @cols = (SELECT ', CASE WHEN '''+CONVERT(CHAR(10),Event_Day,112)+''' BETWEEN [Event_Start] AND [Event_Finish] THEN ''X'' ELSE '''' END AS ['+Event_Day_Label+']'+CHAR(13) FROM #days ORDER BY Event_Day FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(max)');

    -- Generate the dynamic SQL

    declare @sql varchar(max)='';

    SELECT @sql = '

    SELECT Event_Name'+@cols+'

    FROM #Events

    WHERE Event_Year='''+ @SelectedYear +'''

    ORDER BY Event_Name';

    -- EXECUTE the dynamic SQL

    EXEC(@sql);

    And the results:

    +------------------------------------------------------------------------------------------------------------------------------------+

    ¦[highlight="#808080"] Event_Name [/highlight]¦[highlight="#808080"] 28-07 [/highlight]¦[highlight="#808080"] 29-07 [/highlight]¦[highlight="#808080"] 30-07 [/highlight]¦[highlight="#808080"] 31-07 [/highlight]¦[highlight="#808080"] 01-08 [/highlight]¦[highlight="#808080"] 02-08 [/highlight]¦[highlight="#808080"] 03-08 [/highlight]¦[highlight="#808080"] 04-08 [/highlight]¦[highlight="#808080"] 05-08 [/highlight]¦[highlight="#808080"] 06-08 [/highlight]¦[highlight="#808080"] 07-08 [/highlight]¦[highlight="#808080"] 08-08 [/highlight]¦[highlight="#808080"] 09-08 [/highlight]¦[highlight="#808080"] 10-08 [/highlight]¦[highlight="#808080"] 11-08 [/highlight]¦

    +------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------+-------¦

    ¦ AA ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ X ¦ X ¦ X ¦ X ¦ ¦ ¦ ¦ ¦

    [highlight="#E0E0E0"]¦ BB ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ X ¦ X ¦ X ¦ X ¦ ¦[/highlight]

    ¦ CC ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ X ¦ X ¦ X ¦ ¦ ¦ ¦ ¦ ¦

    [highlight="#E0E0E0"]¦ DD ¦ ¦ ¦ ¦ X ¦ X ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦[/highlight]

    ¦ EE ¦ X ¦ X ¦ X ¦ X ¦ X ¦ X ¦ X ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦ ¦

    +------------------------------------------------------------------------------------------------------------------------------------+

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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..

  • r_slot (4/9/2013)


    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

    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 Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • Viewing 15 posts - 1 through 15 (of 23 total)

    You must be logged in to reply to this topic. Login to reply