CODEWITHSUNDEEP
sqlsql-server-2008rowgrouping
user1636130
user1636130

Reputation: 1683

Grouping rows and setting equal to value in next row using SQL

I have a table of data that looks like the following:

ID |   EventName   |     StartTime      |     EndTime     |
 1       Event1      2012-08-08 10:00:00         ???
 2       Event1      2012-08-08 10:15:00         ???
 3       Event1      2012-08-08 11:35:00         ???
 4       Event2      2012-08-08 11:50:00         ???
 5       Event2      2012-08-08 12:05:00         ???
 6       Event1      2012-08-08 12:23:00         ???
 7       Event1      2012-08-08 12:40:00         ???
 8       Event2      2012-08-08 13:47:00         ???

This data is meant to show that Event 1 started at 10 OClock and ran up until 11:50 (The Start time of Event 2). Event 2 then ran from 11:50 till 12:23 at which point Event 1 started up again.

I want to group blocks of the same events and set the end times of the event to the start time of the next event. The resulting table should look like this:

EventName   |        StartTime         |        EndTime        |
 Event1         2012-08-08 10:00:00        2012-08-08 11:50:00 
 Event2         2012-08-08 11:50:00        2012-08-08 12:23:00
 Event1         2012-08-08 12:23:00        2012-08-08 13:47:00
 Event2         2012-08-08 13:47:00        NULL

Upvotes: 0

Views: 395

Answers (3)

VikrantY
VikrantY

Reputation: 594

Interesting question: This should work though I have not tested it(EventsTable is the name of the table)

   select eventname,starttime,endtime
   from(
    select first.eventname eventname , min(second.starttime,first.starttime) starttime,                 second.eventname secondevent,first.starttime endtime
    from eventstable first, eventstable second where first.id=(second.id-1)
    ) where eventname != secondevent

Upvotes: 0

LukeHennerley
LukeHennerley

Reputation: 6444

As somebody said using a cursor would be a good way to do this - I have tested this and supplied a test table in table variable. Please put this in a query window and test it, replace the variables with you table name.

@Events contains:

Event 1 - 10:00

Event 1 - 10:15

Event 1 - 11:35

Event 2 - 11:50

Event 1 - 11:55

The output is:

Event 1 10:00 11:50

Event 2 11:50 11:55

Event 1 11:55 NULL

DECLARE @Events AS TABLE
    (
      ID INT IDENTITY(1, 1) ,
      EventName VARCHAR(50) ,
      StartTime DATETIME ,
      EndTime DATETIME
    )

INSERT  INTO @Events
        ( EventName ,
          StartTime ,
          EndTime
        )
VALUES  ( 'Event1' ,
          '2012-08-08 10:00:00' ,
          NULL  
        )
INSERT  INTO @Events
        ( EventName ,
          StartTime ,
          EndTime
        )
VALUES  ( 'Event1' ,
          '2012-08-08 10:15:00' ,
          NULL  
        )
INSERT  INTO @Events
        ( EventName ,
          StartTime ,
          EndTime
        )
VALUES  ( 'Event1' ,
          '2012-08-08 11:35:00' ,
          NULL  
        )
INSERT  INTO @Events
        ( EventName ,
          StartTime ,
          EndTime
        )
VALUES  ( 'Event2' ,
          '2012-08-08 11:50:00' ,
          NULL  
        )
        INSERT  INTO @Events
        ( EventName ,
          StartTime ,
          EndTime
        )
VALUES  ( 'Event1' ,
          '2012-08-08 11:55:00' ,
          NULL  
        )
DECLARE @CurrentEvent AS VARCHAR(100) ,
    @CurrentStartTime AS DATETIME ,
    @EndTime AS DATETIME ,
    @CursorEvent AS VARCHAR(100) ,
    @EventsToDelete AS INT
SET @EventsToDelete = ( SELECT  COUNT(*)
                        FROM    @Events
                      )
SELECT TOP 1
        @CurrentEvent = EventName ,
        @CurrentStartTime = StartTime
FROM    @Events
DECLARE EventsCursor CURSOR
FOR
    SELECT  EventName ,
            StartTime
    FROM    @Events
    ORDER BY EndTime ASC
OPEN EventsCursor
FETCH NEXT FROM EventsCursor INTO @CursorEvent, @EndTime
WHILE @@FETCH_STATUS = 0 
    BEGIN
        IF NOT @CursorEvent = @CurrentEvent 
            BEGIN
                INSERT  INTO @Events
                        ( EventName ,
                          StartTime ,
                          EndTime
                        )
                VALUES  ( @CurrentEvent ,
                          @CurrentStartTime ,
                          @EndTime  
                        )
                SET @CurrentEvent = @CursorEvent
                SET @CurrentStartTime = @EndTime
            END
           FETCH NEXT FROM EventsCursor INTO @CursorEvent, @EndTime 
    END
CLOSE EventsCursor
DEALLOCATE EventsCursor

DELETE  FROM @Events
WHERE   ID < @EventsToDelete

SELECT * FROM @Events ORDER BY StartTime

SQL Fiddle

Upvotes: 3

aF.
aF.

Reputation: 66697

You may use a cursor to fetch each row at a time.

While fetching:

  1. Get the eventname and starttime from the first fetched row
  2. Get the endtime from the first fetched row with a diferent eventname (this row as the next eventname and stattime as well).
  3. Insert eventname, starttime and endtime in a #temptable.

In the final, select the rows from the #temptable.

Upvotes: 0

Related Questions