Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

timeline in cross-tab?? Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 12:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:25 AM
Points: 73, Visits: 158
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-7 28-7 29-7 30-7 31-7 1-8 2-8 3-8 4-8 5-8 6-8 7-8 8-8 9-8 10-8 11-8
AA X X X X
BB X X X X
CC X X X
DD X X
EE X X X X X X X X

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.
Post #1439977
Posted Monday, April 8, 2013 4:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 36,994, Visits: 31,513
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1440058
Posted Monday, April 8, 2013 4:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 1,787, Visits: 5,696
Can you explain what role #Editions will play in producing the results?

MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1440063
    Posted Monday, April 8, 2013 5:46 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 2:42 PM
    Points: 3,545, Visits: 7,648
    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_Year	int
    DECLARE @SQL nvarchar( 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.
    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?

    Forum Etiquette: How to post data/code on a forum to get the best help
    Post #1440078
    Posted Monday, April 8, 2013 5:54 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 12:42 PM
    Points: 1,787, Visits: 5,696
    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:

    ┌────────────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┬───────┐
    Event_Name 28-07 29-07 30-07 31-07 01-08 02-08 03-08 04-08 05-08 06-08 07-08 08-08 09-08 10-08 11-08
    ├────────────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┤
    │ AA │ │ │ │ │ │ │ │ X │ X │ X │ X │ │ │ │ │
    │ BB │ │ │ │ │ │ │ │ │ │ │ X │ X │ X │ X │ │
    │ CC │ │ │ │ │ │ │ │ X │ X │ X │ │ │ │ │ │
    │ DD │ │ │ │ X │ X │ │ │ │ │ │ │ │ │ │ │
    │ EE │ X │ X │ X │ X │ X │ X │ X │ │ │ │ │ │ │ │ │
    └────────────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┘


    MM


  • MMGrid Addin
  • MMNose Addin


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

  • Post #1440081
    Posted Monday, April 8, 2013 9:33 PM
    SSC Eights!

    SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

    Group: General Forum Members
    Last Login: Yesterday @ 8:20 PM
    Points: 996, Visits: 3,023
    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
    name	year	day1	day2	day3	day4	day5	day6	day7	day8	day9	day10	day11	day12	day13	day14	day15	day16
    2008 7-8 8-8 9-8 10-8 11-8 12-8 13-8 14-8 15-8 16-8 17-8 18-8 19-8 20-8 21-8 22-8
    AA 2008 X X X X
    BB 2008 X X
    CC 2008 X
    2012 27-7 28-7 29-7 30-7 31-7 1-8 2-8 3-8 4-8 5-8 6-8 7-8 8-8 9-8 10-8
    AA 2012 X X X X
    BB 2012 X X X X
    CC 2012 X X X
    DD 2012 X X
    EE 2012 X X X X X X X X


    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
    Post #1440125
    Posted Tuesday, April 9, 2013 12:39 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Monday, March 3, 2014 12:25 AM
    Points: 73, Visits: 158
    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
    Post #1440164
    Posted Tuesday, April 9, 2013 12:42 AM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Monday, March 3, 2014 12:25 AM
    Points: 73, Visits: 158
    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
    Post #1440166
    Posted Tuesday, April 9, 2013 2:03 PM
    Valued Member

    Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

    Group: General Forum Members
    Last Login: Monday, March 3, 2014 12:25 AM
    Points: 73, Visits: 158
    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
    Post #1440551
    Posted Tuesday, April 9, 2013 2:16 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 2:42 PM
    Points: 3,545, Visits: 7,648
    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
    If you still have doubts on the code, feel free to ask.



    Luis C.
    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?

    Forum Etiquette: How to post data/code on a forum to get the best help
    Post #1440558
    « Prev Topic | Next Topic »

    Add to briefcase 123»»»

    Permissions Expand / Collapse