Returning the min value of a record while grouping by event

I don't think grouping will work for your case. You will need a stored procedure to manipulate a cursor which will create a secondary table skipping the records that are consecutive. This is a stored procedure that will iterate through your records one by one:

-- =============================================
-- Create procedure with CURSOR OUTPUT Parameters
-- =============================================
-- drop the store procedure if it already exists
IF EXISTS (SELECT name
         FROM   sysobjects
         WHERE  name = N'udpGetEvents'
         AND         type = 'P')
      DROP PROCEDURE udpGetEvents
GO


-- create the store procedure
CREATE PROCEDURE udpGetEvents
      @procCursor CURSOR VARYING OUTPUT
AS
SET @procCursor = CURSOR FOR
      select * from events
OPEN @procCursor
GO


-- =============================================
-- example to execute the store procedure
-- =============================================
DECLARE @testCursor CURSOR

EXEC udpGetEvents @procCursor = @testCursor OUTPUT

WHILE (@@FETCH_STATUS = 0)
BEGIN
    FETCH NEXT FROM @testCursor
    PRINT 'put user defined code here'
END

CLOSE @testCursor

DEALLOCATE @testCursor
GO

Cheers,

jangeador

This is more specific. You can run it in the sql query analyzer to see the results, I had to use an extra column. EventID to distinguish the events, however you can use a key of the event name and the start and end times combined to id an event:

Declare @id char(10), @id2 int, @EventName char(10), @EventName2 char(10), @Start datetime, @End datetime

PRINT 'Events Report'

Declare events_cursor CURSOR FOR
      SELECT [id], EventName
      FROM Events
      ORDER By StartTIme

OPEN events_cursor

FETCH NEXT FROM events_cursor
into @id , @eventname

while @@fetch_status = 0
BEGIN
      
      WHILE @eventname = @eventname2
      BEGIN
            FETCH NEXT FROM events_cursor
            into @id, @eventname
      END

      --The ID now contains a valid record to show
      --you can manipulate this however you want
      SELECT * FROM events where [id]=@id

      SET @eventname2 = @eventname
      SET @id2 = @id

      FETCH NEXT FROM events_cursor
            into @id, @eventname

END

CLOSE events_cursor
DEALLOCATE events_cursor

GO

I currently don't have a compiler loaded on our server (we are actively trying to get one loaded) so that we can begin using stored procedures.  Is there any way to do this through an ASP page by looping through a recordset.  I realize this isn't as efficient as a stored procedure but could be a temporary fix until we are able to get the stored procedure to work.

Thanks.

That worked perfectly!  I was thinking about it in the wrong way.  Your help cleared that up.

Thanks,
BSDT

Thanks for the points, very glad to be of help!

jangeador