Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Returning the min value of a record while grouping by event

Posted on 2005-03-23
6
Medium Priority
?
263 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

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.

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
Introduction to Processes
Screencast - Getting to Know the Pipeline

578 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