?
Solved

Returning the min value of a record while grouping by event

Posted on 2005-03-23
6
Medium Priority
?
252 Views
Last Modified: 2010-04-17
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?
0
Comment
Question by:BSDT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 1

Expert Comment

by:jangeador
ID: 13618973
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
 
LVL 1

Expert Comment

by:jangeador
ID: 13619267
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
 

Author Comment

by:BSDT
ID: 13621197
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Accepted Solution

by:
jangeador earned 500 total points
ID: 13622035
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
 

Author Comment

by:BSDT
ID: 13627215
That worked perfectly!  I was thinking about it in the wrong way.  Your help cleared that up.

Thanks,
BSDT
0
 
LVL 1

Expert Comment

by:jangeador
ID: 13627712
Thanks for the points, very glad to be of help!

jangeador
0

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Introduction to Processes

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question