Returning the min value of a record while grouping by event

I am using the following table:

Date       EventName        StartTime      EndTime
3/1/05   Work                 8:00am          8:30am
3/1/05   Meeting             8:30am          9:00am
3/1/05   Work                 9:00am          9:30am
3/1/05   Work                 9:30am         10:00am
3/1/05   Work                 10:00am        10:30am
3/1/05   Lunch                10:30am        11:00am

It maintains a schedule for staffed employees and must be stored in 30 minute increments.  However, I have a requirement to represent the schedule with only the first instance of the event displayed.  Like this:

3/1/05        Work            8:00am
3/1/05        Meeting        8:30am
3/1/05        Work            9:00am
3/1/05        Lunch           10:30am

I have tried to group by the event and return the first or min value of that event so that I get only the first start time of that event, but the recordset returns:

3/1/05     Work           8:00am
3/1/05     Meeting        8:30am
3/1/05        Lunch           10:30am

I need it to pull in the 2nd start of the event Work.  

Anyone help?
BSDTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jangeadorCommented:
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
0
jangeadorCommented:
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


0
BSDTAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jangeadorCommented:
Yes, that's basically what I am doing in the Stored Procedure (Sql's Cursor = ADO Recordset). This is the Pseudo code, for I am not on my development machine right now (IF this is not enough, please let me know, and I will code something as soon as I get to my dev machine:

'Declare a tempEvent variable and initialize it to ""
'Declare a recordset will all the fields from the events table ordered by time
'Open the recordset
'Start looping through the recordset until EOF
     'Inner loop
     Do while RS.Fields("EventName") = tempEvent
           'If you get to this part is because the
           'events are equal. You can keep moving through the recordset
           rs.movenext
     Loop

     'When you come out of the inner loop you have the record you want to show
     'Put the code here to display the record

      'Then keep moving by resetting the variables
      tempEvent = rs.Fields("EventName")
      rs.MoveNext
'End Main Loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BSDTAuthor Commented:
That worked perfectly!  I was thinking about it in the wrong way.  Your help cleared that up.

Thanks,
BSDT
0
jangeadorCommented:
Thanks for the points, very glad to be of help!

jangeador
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.