Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    merging rows, howto

    Hello,

    I would like to merge events if they have EXACTLY the same teachers and groups and the enddate of the first is the same as the startdate of the next.
    The results should then be one event with its own startdate and with the enddate of the next(see example).
    Tables :
    Events( Id, StartDate, EndDate)
    TeachersPerEvent( TeacherId, EventId )
    GroupsPerEvent( GroupId, EventId )

    Lets say I have the following data :
    Events( 1, '2013-06-12 08:30', '2013-06-12 09:30')
    Events( 2, '2013-06-12 09:30', '2013-06-12 10:30')
    TeachersPerEvent( 1, 1 )
    TeachersPerEvent( 2, 1 )
    TeachersPerEvent( 1, 2 )
    TeachersPerEvent( 2, 2 )
    GroupsPerEvent( 1, 1 )
    GroupsPerEvent( 2, 1 )
    GroupsPerEvent( 1, 2 )
    GroupsPerEvent( 2, 2 )

    Because Event 1 has the same enddate as the startdate of event 2 and both events have EXACTLY the same teachers and groups I want to have 1 result and this is something like : EventId : 1, StartDateTime : '2013-06-12 08:30', EndDateTime : '2013-06-12 10:30'

    I already made some joins, now I need to find a way to put in the equation to merge the event-records.

    Can anyone help with this?

    gr Coen Dunnink
    The Netherlands

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Too little sample data.

    If there was Events 3 having same teachers and groups with Events 1 and Events 2,
    what result do you want?
    Events( 1, '2013-06-12 08:30', '2013-06-12 09:30')
    Events( 2, '2013-06-12 09:30', '2013-06-12 10:30')
    Events( 3, '2013-06-12 10:30', '2013-06-12 12:00')

  3. #3
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    merging rows, multiple

    EventId : 1, StartDateTime : '2013-06-12 08:30', EndDateTime : '2013-06-12 12:00'

    The Id of the Event is actually not so important, because all the event are the same( apart from the start- and enddate)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by thepercival View Post
    ...

    The Id of the Event is actually not so important, ...
    But, weren't Id of Events related with TeachersPerEvent.EventId and GroupsPerEvent.EventId?
    And you wrote "I would like to merge events if they have EXACTLY the same teachers and groups ..."

    So, I thought that the relationship of Id of Events with TeachersPerEvent.EventId and GroupsPerEvent.EventId might be important.


    Anyway,

    I want to ask again...
    If there was Events 3 having same teachers and groups with Events 1 and Events 2,
    what result do you want?
    Events( 1, '2013-06-12 08:30', '2013-06-12 09:30')
    Events( 2, '2013-06-12 09:30', '2013-06-12 10:30')
    Events( 3, '2013-06-12 10:30', '2013-06-12 12:00')

  5. #5
    Join Date
    Jan 2003
    Location
    The Netherlands
    Posts
    149

    answer

    on your first question : YES

    the relationship between events.id and groupsperevent.eventid/teachersperevent.eventid is important.

    On the screen, in this case, I only show 1 event, so it does not matter if it is event 1, 2 or 3.
    as long the startdatetime is 08:30 and the enddatetime is 12:00.

    Clear?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Do you want the result like this?
    Code:
    EventId StartDateTime    EndDateTime
    ------- ---------------- ----------------
          1 2013-06-12 08:30 2013-06-12 12:00
    If so, recursive common table expression might be an answer.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •